h1

Restoring an SQL Express database on another computer

February 5, 2008

Scenario: You have a Microsoft SQL Express 2005 database on one computer (A) and want to restore it on to another computer (B). Perhaps you want to work on the database remotely, like on your laptop on a flight.

I found there are a couple of tricks you have to do to get the database from computer A restored on to computer B. Remember, I’m an SQL newbie, so this may be blatantly obvious to those who use SQL all the time, but maybe it’ll help someone else who encountered error messages when they tried to restore a database.

Prerequisites:

  • Microsoft SQL Server Management Studio Express and Microsoft SQL Server 2005 Express MUST be installed on both computers.
  • You should know how to back up an SQL Express database to a file.
  • Stop any services that access the database you want to restore, if applicable.

Here’s what you have to do in Microsoft SQL Server Management Studio Express:

  1. Backup the database on computer A to a file.
  2. If necessary, transfer the backed up database file to somewhere on computer B.
  3. Check that an SQL Express database of the same name exists on computer B. If it doesn’t, create it.
  4. On computer B, right-click on the database name, then select Tasks > Restore > Database.
  5. On the Restore Database window, select the database you’re restoring to (To database field).
  6. Select the From device option, then click the […] button to the right of this field to open the Specify Backup window.
  7. Make sure the Backup Media selection is File.
  8. Click Add, navigate to the location of the backup file, select it, then click OK.
  9. Click OK on the Specify Backup window to close it and return to the Restore Database window.
  10. Select the check box for the database backup you’re restoring. At this point you might think you can just click OK and everything will work. Well, it very likely won’t. You’ll get cryptic error messages about not being able to restore.
  11. In the left pane, click Options.
  12. Select the Overwrite the existing database check box.
  13. For first file listed, click the […] button to its right, navigate to the database location on computer B, then select its *.mdf file and click OK. By default, databases are stored under: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.
  14. For the second file listed, click the […] button to its right, navigate to the database location on computer B (same location as in the previous step), then select its *_log.ldf file and click OK.
  15. Click OK on the Restore Database window. The restore should now happen without error.
  16. Restart any services that access the database, if applicable.

31 comments

  1. […] See also: Restoring an SQL Express database on another computer […]


  2. thanks man you save me :)!


  3. I don’t think you have to go via the backup path.
    That’s what I did to copy our database from one machine to another:

    CompA:
    Detach the database (this makes it unavailable to users and available for copying)
    Copy the mdf and the log file to whereever

    CompB:
    Copy mdf and log file into your data folder
    Attach the database

    Reinhard


  4. That sounds a lot easier! I’ll try that next time. Thanks Reinhard.


  5. Perfect steps..! Thanks


  6. I just came across this post and thanks for the tip! I can’t detach the database onmy hosted site, so I have to go the backup/restore route.


  7. Nice one! Reinhard comment was also very good.

    Thanks James


  8. Awesome, thankyou!


  9. Thanks! You saved my butt!


  10. Awesome! Perfect steps, instant success.
    Thanks!


  11. Hi i have problem with restore database with sql express 2005.Could you help me!!

    the sms appear restore failed for server ‘HOME-1\SQLEXPRESS’(Microsoft.sqlServer.Express.Smo)
    Addition information:
    system.data.SqlClient.SqlError:Directory lookup for the file”:\DATABASE\happyNight.mdf”failed with the operating system error 2(the system cannot find the specified.).(Mirsoft.SqlServer.Express.Smo)

    THANKS


  12. Great – Next time you’re in Cape Town I’ll buy you TWO cups!


  13. Thanks! The ‘options’ part is what I was missing and I couldn’t figure it out on my own (and sometimes I’m very smart). Good job.


  14. […] Restoring an SQL Express database on another computer (5,940) […]


  15. Thanks for this article — it really saved my week. Could you please tell me how can I restore a database not from a back up file but form the original data file in a hard drive that is attached to the same PC, originally in the HDD was installed w2000 crashed could not use any more as primary hard disk, had to reinstall everything in a new hard drive, restored another database from a back up file, but a second database did not have a back up file. I have all the original files contained under MSQL/DATA/. Many thanks for your help.


  16. Hi Hugo

    I’m not an SQL expert (I’m barely a novice!), so I can’t help you with that. I suggest you try Googling your problem and checking out various user forums where someone has no doubt documented the instructions for doing what you need to do.

    Sorry I can’t help you any further.

    –Rhonda


  17. HI
    I took backup a database in sql server 2005 in windows 7. and i tried to restore the backup file in sql server 2005 in windows xp sp2..
    i unable to do this..
    it causes that operating system error 3 in xp

    What can i do for this problem..

    Plz replay…


  18. help…thanks a lot


  19. […] views’ list for all time that didn’t make it to the over 5,000 views for 2010 list (Restoring an SQL Express database on another computer). Two posts had more than 5000 views in 2010 that weren’t on the 10,000+ all time views […]


  20. This worked perfectly for me with SQL Server RS 2008 Express version, cheers :)


  21. deacthing database more correct ……. it copies all the data and tables too …


  22. Can anyone help me ….

    I have a computer(A) which is connected with 10 more computers(1,2,3,4,5,6,7,8,9,10)

    I have installed Sql Server 2005 in computer (A).

    After making a setup of a software in visual studio 2005 , I installed it in all computer (1,2,3,4,5,6,7,8,9,10). now how can these (1,2,3,4,5,6,7,8,9,10) computers recognise Computer (A) ,,,,, Please help me with the steps which i need to do…

    Thank You !!


  23. Can anyone help me ….

    I have a computer(A) which is connected with 10 more computers(1,2,3,4,5,6,7,8,9,10)

    I have installed Sql Server 2005 in computer (A).

    After making a setup of a software in visual studio 2005 , I installed it in all computer (1,2,3,4,5,6,7,8,9,10). now how can these (1,2,3,4,5,6,7,8,9,10) computers recognise Computer (A) to save data inserted by computers (1,2,3,4,5,6,7,8,9,10)

    like
    computer 1, user entered
    Name = AAAA
    Roll = 1234

    Computer 2, User entered
    Name = SPKN
    Roll = 6754

    it should be saved in Computer (A)
    TABLE: CCC
    Name Roll
    AAAA 1234
    SPKN 6754

    Please help me with the steps which i need to do to do so…..

    Thank You !!


  24. Thank you a lot , your site was so much helpful !


  25. Thank You !! :) :)


  26. Thanks for the help.


  27. nice easy instructions that work – Bruce


  28. I love you! <3 =D


  29. These are great instructions, in particular the hidden options (steps 11-12) that cause everything to stall. Recently I created a new test environment using SQL 2008 Express, the process is basically similar but note that:
    1. Installing SQL Express 2008 (http://www.microsoft.com/en-us/download/details.aspx?id=1695) doesn’t automatically install Management Studio Express 2008. You have to download a separate installer. The regular package linked from the main page wouldn’t work for me (and a thousand other bloggers I read) but the R2 package did (http://www.microsoft.com/en-us/download/details.aspx?id=22985).
    2. Once you get it installed and set up, the backup file has to be located somewhere on C:/ The navigation tree is VERY rudimentary (same as 2005).


  30. Thanks for adding those instructions and links to SQL Express 2008, Sarah!


  31. Thank u very very much… it works :)



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: