Tip

Restoring a database from another SQL Server

There are many reasons that you would want to move a database from one server to another, disaster recovery, refreshing a test environment, data analysis or maybe something else. Restoring the database is the easy part, but then you may be left with mismatched logins and database users. In this tip we will look at some of steps to go through when restoring a database from a different server.


The restore

The first step in the process is to restore the database. We talked about restoring databases in a previous tip and the different commands that are used to restore databases. We will take a look at a couple of the commands that can be used to look at the contents of the backup files as well as the commands to perform the restore. Restores can be accomplished by either using T-SQL commands or using Enterprise Manager. We will take a look at how the restore can be accomplished using T-SQL.

RESTORE HEADERONLY

To determine what is stored on the backup files you can run this command in Query Analyzer.

RESTORE HEADERONLY FROM DISK = 'C:\SQL\Backup\North.bak'

This command allows you to see the backup header information for all backup sets on a particular backup device. The information included in this is shown in the following table. This command is useful when you have multiple files to work with or if you get a backup file from a server that you do not manage.

RESTORE FILELISTONLY

This is another command

    Requires Free Membership to View

that can be run on the backup files.
RESTORE FILELISTONLY FROM DISK = 'C:\SQL\Backup\North.bak'

This command allows you to see a list of the database and log files contained in the backup set, what filegroup they are in and also the size of the data and log files. For our restore process the LogicalName and PhysicalName are key data elements. These will be used in the next step when we actually perform the restore.

RESTORE

To restore the database we may need to use the MOVE option to move the physical files to a different location and also the NORECOVERY option if we want to restore multiple backup files (i.e. full, differential and logs).

Let's say we are moving the database from one server to another and the logical drives are different. In our output above the data files were located in the C: \Program Files\Microsoft SQL Server\MSSQL$TEST\data directory, but we now need to restore to the D: and E: drive on a different server. The command would look like this.

RESTORE DATABASE NORTH
   FROM DISK = 'C:\SQL\Backup\North.bak'
   WITH MOVE 'NORTH_Data' TO 'D:\SQL\Data\North_Data.mdf', 
        MOVE 'NORTH_Log' TO 'E:\SQL\Log\North_Log.ldf'

If we also need to restore and move both a full, differential and log backups the commands would look like this.

RESTORE DATABASE NORTH
   FROM DISK = 'C:\SQL\Backup\North.bak'
   WITH NORECOVERY,
  MOVE 'NORTH_Data' TO 'D:\SQL\Data\North_Data.mdf', 
    MOVE 'NORTH_Log' TO 'E:\SQL\Log\North_Log.ldf'

RESTORE DATABASE NORTH
   FROM DISK = 'C:\SQL\Backup\North_Diff.bak'
   WITH NORECOVERY,
  MOVE 'NORTH_Data' TO 'D:\SQL\Data\North_Data.mdf', 
     MOVE 'NORTH_Log' TO 'E:\SQL\Log\North_Log.ldf'

 RESTORE LOG NORTH
    FROM DISK = 'C:\SQL\Backup\North_Log.bak'
    WITH RECOVERY,
  MOVE 'NORTH_Data' TO 'D:\SQL\Data\North_Data.mdf', 
     MOVE 'NORTH_Log' TO 'E:\SQL\Log\North_Log.ldf'


Users

Now that the database has been successfully restored we need to make sure our users and logins match on the server.

When you restore backups from another server their will most likely be mismatched login and user information. The login information is stored in the sysxlogins table in the master database. This table contains a column that holds the SID (Security Identifier) which is tied to a specific login. A corresponding table sysusers is stored in each user database and uses the SID to determine if a login has database access. So even though you may already have the same logins on your new server the SID may not match. What makes this even more confusing is that you can see the actual names of the logins and users, so you would think they would automatically match up, but the SID is what is actually tying the security together not the name.

So if the standard login already exists on the server and the user exists in the database you can use this stored procedure to relink the standard login and user.

sp_change_users_login

This stored procedure has three options Auto_Fix, Report and Update_One. Below is a brief description, but more can be found in SQL Server 2000 Books Online.

  • The Auto_Fix option will link users and logins that have the same name.
  • The Report option will show you a list of users in the current database that are not linked to a login.
  • The Update_One option allows you to link a user and login that may not have the same exact name.

Note: To link Windows logins see more information under Moving Logins section below.

REMOVING USERS

If there are users in the database and there is not a corresponding login on the server, you can use the following command to remove the user from the database and cleanup the user list.

 sp_revokedbaccess 'NorthDomain\Mike'

LOGINS

On the other end of the security there are logins. To find out what access a login has on your server you can run the following command. This will display a list of databases the login has access to as well as other info about the login such as the SID.

 sp_helplogins 'NorthDomain\Mike'

If there is a user in your database and there is not a corresponding login on the server you can use the following commands to create the new login.

If you are creating a standard SQL Server login you would use this command to create the login.

 
 sp_addlogin

If you are creating a login that will use Windows authentication use this command.

 
 sp_grantlogin 'NorthDomain\Mary'

To change the default database for a login use this command for both Windows or standard logins.

 
 sp_defaultdb 'NorthDomain\Mary', 'master'

MOVING LOGINS

In some cases you may need to duplicate the entire login list from one server to another. This can be achieved manually by running the above commands, by using DTS or you can use the stored procedure that was developed by Microsoft (see link below). When you create the logins manually or use DTS the original SID is not kept and a new SID is created as the login is being created, therefore you will need to link the logins and users for your database. See Microsoft Knowledge Base article 246133 How to: Transfer logins and passwords between instances of SQL Server for more information.

If your NT login and user names do not match you can use the following commands to link your NT login and user information. Microsoft does not recommend updating system tables directly, so you should only use this if you totally understand what you are doing and how to recover if there is a problem. You also need to change the server setting to allow updates to system tables.

DECLARE @sysxlogins_sid VARBINARY(85)
SELECT @sysxlogins_sid = sid FROM master.dbo.sysxlogins WHERE name = 'NorthDomain\Joe'
UPDATE sysusers SET sid = @sysxlogins_sid WHERE name = 'Joe'


Changing database owner

The last thing you may want to do is change the owner of the database after the restore has been completed. When a database gets restored the user running the restore command becomes the owner of the database. To change the owner, issue this command.

sp_changedbowner 'sa'


Summary

As you can see there is a little more to the restore process then just restoring your backup files. Keeping logins and users in sync is not always a simple task, unless the machines are completely identical, which rarely happens. The above steps along with the commands should be all you need to get your databases restored and your users linked properly. For additional information you can always refer to SQL Server 2000 Books Online.


About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Greg, who serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

This was first published in June 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.