Safe Transit

Restoring your database is one of the most important tasks you might ever have to perform. You plan your recovery operation knowing that if a disaster strikes, your organization's future might depend on your ability to make your data available quickly. In "All About Restore," May 2002, InstantDoc ID 24340, I discussed how to restore a database on top of an old version or to a new location on the same SQL Server. But in some situations, you might want to restore a database backup onto an entirely new server. You might simply want to create a separate SQL Server installation for testing, training, or debugging purposes. You can also restore a SQL Server 7.0 database to a separate SQL Server 2000 server to perform an upgrade of a single database from SQL Server 7.0 to 2000. But in a disaster situation in which your entire system is damaged, you might need access to the data as quickly as possible.

When you move a backup of a SQL Server database from one server to another, you encounter some specific challenges. A common problem is that in the restore process, usernames and login names can be mismatched. Let's look at why usernames and login names are important, why mismatched names are a problem, and how to use a special procedure called sp_sidmap to avoid such problems.

Who's Who?
The most important concern you have when you restore a database backup to a new system is making sure that only the appropriate users have access to the restored database. Not only is this complex problem difficult to solve, but it's also difficult to understand. To make sure that a newly restored database allows access to the same users as the original database did, you have to clearly understand the distinction between a SQL Server login name and a database username, both of which need to be properly set up before users can access the data. One reason this distinction between login names and database usernames is difficult to understand is that even SQL Server Books Online (BOL) doesn't always clearly distinguish between these two levels of access into a SQL Server system. To understand the details, you also need to understand the difference between SQL Server authentication and Windows authentication. Authentication is frequently misunderstood but is crucial for a correct restore operation onto a new server—however, the topic is beyond the scope of this article. For information about authentication modes and planning your system security implementation, see Morris Lewis's articles "Security in SQL Server 7.0," December 1999, InstantDoc ID 7442, and "Creating a Manageable Security Plan," October 2000, InstantDoc ID 15446.

Users and the Restore Process
The distinction between login name and username is an important concept. Having a login name for SQL Server only lets you in the front door. To get into a database, you must also have a username in that database.

To access any objects in the database, your username must have appropriate rights. (For more information about the distinction between logins and usernames, see the Web-exclusive sidebar "Database Access," InstantDoc ID 25984, at http://www.sqlmag.com.)

So, why is the difference between login names and database usernames important to restoring a database? When you restore a database, all data in that database, including all the system tables, is copied to the new location. This new copy includes the sysusers table, which contains the list of valid usernames in the database and SIDs that each username corresponds to. The SIDs in sysusers correspond to usernames on the original system. Typically, a SID in sysusers maps directly to a SID in the sysxlogins table in the master database of the original server, and the original sysxlogins table also contains the login name corresponding to that SID. A problem you might encounter when you restore a database to a new system is that the login names and SIDs on the original system might not be the same as the login names and SIDs already on the new system.