Restoring an SQL Express database on another computerFebruary 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.
- 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:
- Backup the database on computer A to a file.
- If necessary, transfer the backed up database file to somewhere on computer B.
- Check that an SQL Express database of the same name exists on computer B. If it doesn’t, create it.
- On computer B, right-click on the database name, then select Tasks > Restore > Database.
- On the Restore Database window, select the database you’re restoring to (To database field).
- Select the From device option, then click the […] button to the right of this field to open the Specify Backup window.
- Make sure the Backup Media selection is File.
- Click Add, navigate to the location of the backup file, select it, then click OK.
- Click OK on the Specify Backup window to close it and return to the Restore Database window.
- 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.
- In the left pane, click Options.
- Select the Overwrite the existing database check box.
- 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.
- 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.
- Click OK on the Restore Database window. The restore should now happen without error.
- Restart any services that access the database, if applicable.