Skip to content

Categories:

Restoring an SQL Express database on another computer

These instructions assume you have backed up to a file using SQL Server to do the backup.  The file will have a .bak file extension. These instructions apply to Microsoft SQL Server Management Studio Express 2005 and 2008, which are available as a free downloads from the Microsoft site.

Optional – Retaining the old database when restoring to point in time

This is an optional step that you can use if you are restoring a database back to a point in time (due to data entry error for example) and will retain the existing database in case you wish to go back to it for any reason.

  1. Using SQL Server Management studio, in the object explorer under the “Databases” node, right click your database and choose “rename”.  Give it another name.
  2. Execute the following code:
    • USE master
    • GO
    • ALTER DATABASE [YourDatabaseName]
    • SET OFFLINE WITH ROLLBACK IMMEDIATE
    • GO
  3. The database will now be offline (you may need to right click the database and select “refresh” in the object explorer to see this)
  4. Right click the database select “Properties”
  5. Copy the top value under “Path”
  6. Browse to this path using Windows Explorer.  You will see the database files.
  7. Rename the database files to match the new database name you provided above.
  8. If you wish to go back to this database at any time, you may need to:
    1. Go back into properties and alter the file names under the “File Name” to match the new file names you gave to the database files
    2. Bring the database back online (you can right click and select Tasks – > Set Online

Performing the restore

  1. Copy the backup file to the target SQL server file system
  2. Using SQL Server Management studio, in the object explorer right click the “Databases” node and select “New Database…”
  3. Name the database how you like and click ok.
  4. In the object explorer, right click the database you just created and select “Properties”
  5. In the left hand side, select “Files”
  6. Copy the two values under “Path” and the two values under “File Name”. Paste into Notepad, joining the File Name to the Path.  You will need them later.
  7. In the object explorer, right click “Databases” again and choose “Restore Database…”
  8. In the “To database” field, select the database you just created.
  9. Select “From device”
  10. Now select your .bak file as the source.
  11. In the window below, your backed up database will appear.  Check the checkbox to the left of it.
  12. Select “Options” from the left.
  13. Make sure “Overwrite the existing database” is checked.
  14. For first file listed in “Restore the database files as”, paste the locations that you copied in step 6.  Make sure the log one goes into the log field and vice versa.
  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.

Posted in Uncategorized.

Tagged with , .


6 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Christian Louboutin says

    great information you write it very clean. I am very lucky to get this tips from you.

  2. MBT Men Shoes says

    Hello everyone thanks for

    good information.

  3. mbt shoes says

    Good job. I’m definitely going to bookmark you!

  4. registry cleaner reviews says

    I’ve already bookmark this article and will definitely refer this article to all my close friends and colleagues. Thanks for posting!

  5. finance software says

    very well

    information you write it very clean. I’m very lucky to get this information from you.

  6. entelfime says

    Aloha i’m fresh on here, I came upon this chat board I find It exceedingly useful & its helped me out alot. I hope to give something back & guide others like it has helped me.

    Cheers, Catch You Later

    _________________
    home cinema installation london