SQL Server Reference Guide

In the last tutorial in this series I explained how to test your backup operations with a restore. That’s really important, and something you should do quite often. In this tutorial we’ll take this to the next step, and learn how to take those backups and restore them to another system entirely, rather than just to the one where you backed it up.

There are a lot of reasons for doing this. For one, you can take the backup on the production system and then restore it to a development or testing system. Another, hopefully less common reason is that you have lost your primary system, so you’ve rebuilt one and are now restoring the database to it.

In any case, you need to move the database and all its data to another system. In this tutorial, I’ll show you how to do that. But we won’t just use a backup and restore operation to do that. There are actually other methods you can use to get not only the data but all of the database objects to another system. And doing this process creates a problem — I’ll also explain what that problem is, and show you how to fix it.

Although I’ll show you how to create a database to “play” with during this tutorial, it’s still important to do this on a testing system. It’s impossible for me to know your environment, and these steps might have unintended consequences. I highly doubt it, mind you, but you never know. Better safe than sorry!

We’re going to discuss three methods for transferring databases to another system in this tutorial. I’m leaving out a fourth method — the “Transfer Database Wizard” or “Copy Database Wizard,” depending on which version of SQL Server you are using. Since those versions are different, I’ll devote an entire article to that feature.

The three methods we’ll look at are using Backup and Restore, detaching and attaching a database, and copying “dead” files. Let’s get started.

Setting up the systems

In the last tutorial I showed you how to do backups and restores when the database was in the “Simple” or “Full” recovery model. I won’t repeat that information here — we’ll just use the Simple model to make the demonstration scripts shorter.

Before we run the scripts, let me mention the environment I’m using. I’m creating and then transferring the database from a SQL Server 2000 system to a SQL Server 2005 system. You can use all of this information whether you have that environment, or any mix of SQL Server 2000, 2005 or 2008. I’ll make sure I call out any differences between the versions while we’re working through the examples. So whether you’re moving from 2000 to 2005, 2005 to 2005, or 2008 to 2008, these examples will work.

Let me mention one note of caution about that, however. You can come from a lower-level version of SQL Server (within limits) to a higher version, but not the other way around. There are techniques for doing that, but we’re not covering those here. SQL Server will automatically “rev up” a lower version to a higher using any of the examples I’m about to show you.

So, on the “Source” system, which in my case happens to be SQL Server 2000, we’ll need to create a server login. Remember from my security series that adding a server login is like giving your users a key to the building and then creating a database user is like giving them a key to an individual office. You need both to allow them all the way through to the database objects.

In SQL Server 2000, you use a stored procedure called sp_addlogin to create a new user. Let’s do that here:

/* This is on the SQL Server 2000 System */
/* First we need a server login on the Source system */
USE master;
GO
EXEC master.dbo.sp_addlogin 
@loginame = N'BackupTestUser'
, @passwd = N'Passw0rd'
, @defdb = N'master';
GO

You can see the variables here — you need a login name that you want, a password (if you’re creating a SQL Server user like I am and not a Windows user) and a default database. For now I’m leaving that as the master database, since we don’t have our test database yet.

Now I’ll switch over to the “Destination” system, and create a server login there as well. Since this one is SQL Server 2005, I’ll use the CREATE LOGIN statement, again with the same parameters:

/* Repeat for the Destination system */
USE master
GO
CREATE LOGIN BackupTestUser 
WITH PASSWORD=N'Passw0rd'
, DEFAULT_DATABASE=master
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

You can still use the older stored procedure if you like, but you should get used to the new syntax, since that works not only in SQL Server 2005 but 2008 as well.

With those server logins created, we’ve now introduced the problem. You can see it when you run this query on both systems:

/* Let's see that user (run on both systems): */
USE master;
GO
SELECT sid
, name
, loginname
FROM syslogins
WHERE name = 'BackupTestUser';
GO

Notice that the numbers for the “sid” (which is short for Security Identification Number) are different between the two systems. We’ll come back to that in a moment.

Now, back to the “Source” system to create the test database we’ll use today:

/* Now we create the test database */
USE master;
GO
CREATE DATABASE TransferTest;
GO

Next, we need to tie out the server login we made a moment ago (we’re still on the “Source” system here) to a new database user. In SQL Server 2000, you can use the sp_grantdbaccess command, and in SQL Server 2005 and higher you use the CREATE USER command. You can find more on the CREATE USER syntax here, but here is the example script for the SQL Server 2000 system:

/* Create a new database user, tie to the server logon */
USE TransferTest;
GO
EXEC dbo.sp_grantdbaccess 
@loginame = 'BackupTestUser'
, @name_in_db = 'BackupTestUser';
GO
/* We'll make that user a DB owner */
EXEC sp_addrolemember 
'db_owner'
, 'BackupTestUser'
GO

And here’s the magic — on the Source system, run the following script to see that user in the database:

/* Let's take a look at that user: */
SELECT sid
, name
, uid
FROM dbo.sysusers
WHERE name = 'BackupTestUser'
GO

Remember the query we ran on the Source system to see the server logins? The “sid” number there is the same one you’ll see in this query. The name doesn’t even matter — the important part is that the “sid” numbers match. You can probably begin to see the issue already. On the Destination system the “sid” number is different than the one on the Source system. When you move the database, however, the “sid” recorded with the database user name will retain the number from the Source system, and they won’t match. This creates a real issue, since you have the name already in the database on the Destination system, so you can’t create it again, but the numbers are different. Don’t worry, I’ll show you how to solve this problem in a moment.

With everything set up, we’re ready to start our transfers.

Transferring Databases using Backup and Restore

Let’s begin with the easiest way to transfer the data. We’ll back up the database on the Source system, copy the backup file to the Destination system, and then restore the database there. First, the backup:

/* First method - Backup and restore */
/* Source System: Backup the database */
BACKUP DATABASE TransferTest
TO DISK = 'c:\temp\TransferTest.BAK'
WITH INIT;
GO

Nothing new here, this is the same thing you saw in the last tutorial. Now copy the backup file to the Destination system, and note where you placed it. In my case, it’s in the c:\temp directory on both. Now the restore command, with a slight difference than what you’ve seen before:

/* Copy backup file to destination system */
/* Destination System: Restore the database */
/* Restore a Complete Backup */
USE master;
GO
RESTORE DATABASE TransferTest
FROM DISK = 'c:\temp\TransferTest.BAK'
WITH REPLACE
, RECOVERY
, MOVE 'TransferTest' 
 TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TransferTest2.mdf'
, MOVE 'TransferTest_log' 
 TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TransferTest2_log.ldf';
GO

The key here is the “WITH MOVE” qualifier. It takes the name of the database file (called the “logical” name) from the Source system and places it on the location of your choice on the Destination system (make sure you select a filename that isn’t already in use!).

But what was the logical name to begin with, and where do you want it to go on the new system? You can use this query on both systems to find the answer:

/* Find the current file locations
Run this in the Source database
And on the Destination system */
SELECT * 
FROM sysfiles;
GO

With the database restored, you’re now ready to fix those “orphaned” database users. I’ll show you how to do that in a moment.

Transferring Databases using detach and attach

A database is actually just a set of files on the hard drive. And the fact that the system “knows” about those files is really just an entry in the master system database. Microsoft has created a stored procedure that will tell the master database to remove those pointers, and the system will “forget” that the database belongs to this server. Those files are now “dead,” meaning that the system isn’t locking them anymore. You can then copy those files to another system, and use another mechanism to “adopt” the dead files onto the new system. In some cases this can be faster than a backup and restore operation.

If you do choose to copy the files, this is the safer way to do it (I’ll show you another in a moment). The stored procedures rolls all of the transactions forward, closes the database, and then makes the changes to master.

So in my test environment on the Destination system I’ve deleted the restored database and started over. I move to the Source system and run the following commands to detach the database:

/* Second method - detach and attach */
/* Source System - detach here */
USE master
GO
EXEC sp_detach_db 'TransferTest'
, 'true';
GO

Pretty simple. Now I copy the files to the directory of my choice (c:\temp) on the Destination system and run the attach process. This is a little different — it doesn’t use a stored procedure, just the CREATE DATABASE command:

/* Destination system: attach the database */
USE [master]
GO
CREATE DATABASE TransferTest ON
( FILENAME = 'C:\Temp\TransferTest.mdf' ),
( FILENAME = 'C:\Temp\TransferTest_log.ldf' )
FOR ATTACH
GO

By the way, you’ll probably want to place those files in the “real” SQL Server data directories. I’m just using this example to show you that the files can go anywhere on the Destination system.

There are some caveats to this process. If the database is being replicated or other select operations are being performed on it, this won’t work. Read more about that in Books Online.

Transferring Databases by copying files

You can use yet another method to copy the files across to another system. This is really just a manual version of the process you just saw. The way you do that is simply to stop SQL Server (you’ll kick off all of your users, nothing will run, people might be unhappy) and then copy the “dead” files to the Destination server. Then just run the last statement in the examples above to adopt the new files into the Destination server. Not my favorite way of doing this, but I have had to do it in drastic circumstances.

Fixing the orphaned users

In all of these methods, you lose the number-pairing between the master.syslogins and databasename.sysusers tables. That creates this orphaned set of users. Even worse, since the graphical tools only show these users when they are “synced” properly, they don’t show up — but when you try to create them in the database again, you get an error. Very frustrating!

So here’s how you can find and fix these users. There’s a simple stored procedure that compares the names in the database to the names in the Destination server:

/* Now the users are "orphaned". Let's see them: */
USE TransferTest;
GO
EXEC sp_change_users_login @Action='Report'
GO

If you’ve been following along with these examples, you’ll see our “BackupTestUser” account in this report. Fixing this is as simple as running the stored procedure again, and giving it the name of the user in the database, and the login on the server you want to tie it to:

/* Fix that user: */
USE TransferTest;
GO
sp_change_users_login 
@Action='update_one'
, @UserNamePattern='BackupTestUser'
, @LoginName='BackupTestUser';
GO

So there you have it. In future tutorials we’ll explore more backup and recovery examples.

InformIT Articles and Sample Chapters

If you’re also in charge of Oracle systems, fear not. We have lots of information on backing that kind of data up in this sample chapter from the book Oracle DBA Automation Scripts.

Books and eBooks

We have even more maintenance information in the Adobe e-book called Microsoft SQL Server 2005 Unleashed.

Online Resources

Microsoft has the syntax for adding a backup device here.