Restoring a MS SQL database from one server to another

I find myself restoring databses from one server to another from time to time. I generally do this when we want to take a snap shot of a live database and use it on a development site.

The easiest way to restore the database from a backup file (.bak) is to use the Microsoft SQL Server Management Studio GUI.

  1. Once logged into the server you want to restore the database to. Expand the database folder in Object Explorer panel so you see all your databases listed.
  2. Right click on the database you want to restore then select
    Tasks -> Restore -> Database…
  3. This will bring up a window titled ‘Restore Database – yourdbname‘. Click the radio button ‘From Database’ followed the ‘…’ button.
    restoreDB2This bring up a window titled ‘Specify Backup’ click the ‘Add’ button this will allow you to browse your computer and locate the backup file (.bak), click ok twice and this will return you back to the ‘Resotre Database – yourdbname
  4. On the ‘Restore Database – yourdbname‘ window ensure the restore check box is checked under ‘Select the backup sets to restore:’
  5. Now we have selected where to restore our back up from we need to set a few paramerters before we can restore. Click on Options from the ‘Select a page’ panel.
  6. On the Options screen we need to check ‘Overwrite the existing database (WITH REPLACE)’ under restore section. Also we need ensure we are restoring the database to the correct files. Ensure the paths to ‘Raw Data’ and Log are pointing to the correct files. This is particularly important if you are running production and development databases of one server.
  7. Now we have everything set click on OK and it will start to restore the database.

This is pretty much all you need to do to restore a database. However, you may find you will have an orphan user that you need to link the database user to a SQL server login.

If you have created the original login name on the SQL server before the datebase restore, then run the following command against the database from the query analyser.

sp_change_users_login 'auto_fix', 'Login'

You must specify the login in the above command.

If however, you have multiple users in the databse which have their own logins, you will have to the orphan them individually as follows:

sp_change_users_login 'update_one', 'user', 'Login'

You must specify the user and login. The login must exist.

One thought on “Restoring a MS SQL database from one server to another”

Leave a Reply

Your email address will not be published. Required fields are marked *