Moving a SQL Server database from one server to another is simple-initially. But there are various methods for moving a database, and some have more advantages than others. Investigating the types of moves ahead of time can make planning for a database migration easier.

It takes essentially three steps to move a database from one server to another: take the database out of production, copy it to another server, and then bring the copy back into production. While you can choose from many methods to accomplish these steps, it can be difficult to complete the move given all the database’s dependencies. In this article I’ll describe the methods for moving the database. In my next installment I’ll discuss the dependencies and additional related objects that you must also move along with the database data.

Moving a Database

When SQL Server has an active database, it locks the underlying data files and they cannot be copied. Therefore if you want to move a database from one server to another, you must either unlock those files or else create a backup copy and prevent any further changes to the database while you are moving the copy.

When you have copied the database to a new SQL Server instance, usually on a new database server, you must then make the database active. In a true database move you will have placed the copied database data and log and other files in new locations. It is always easier to keep exactly the same Windows volume and file structure on the new server as on the old server, though that is not always necessary. If you’ve changed the volume names or file structure, various methods make it possible to recognize the new locations.

SQL Server provides essentially three techniques for moving a database:

  • detach and copy
  • take the database offline and copy the files
  • back up the database and restore it on the other server

Each method has advantages and disadvantages that I’ll describe below.

Detach and Attach a Database

SQL Server 2005 and 2000 support both GUI and stored-procedure approaches to detaching and attaching a database. When you detach a database, SQL Server runs a checkpoint to make sure that all the changed database data in SQL Server’s data cache is written to disk. As part of the detach process, SQL Server removes the database from its registered databases so that the database is no longer recorded as belonging to that SQL Server. Now the files are freed up to copy.

Once a database is detached, no users can connect to it, the files are unlocked, and you are free to copy the files to a new location. When you attach the database to a new SQL Server, you have the option of changing the locations of the data and log files, if you must put them on different volumes.

Detaching and attaching a database is an essential tool for the database administrator and is often a very practical way of moving a database. However, this technique has some disadvantages. In SQL Server 2000, if your database has full-text indexes, you will have to manually move the full-text catalog files to the new database server and make sure that the new SQL Server can recognize their location. In SQL Server 2005, the detach-and-attach method also recognizes the full-text catalog files, and provided you also manually copy them appropriately, attaching the database in a new location will go smoothly. It’s just not as transparent an operation as the backup and restore method.

Taking the Database Offline

You can choose to take a database offline and copy the files to a new location as a method for moving the server. In SQL Server, you can use the ALTER DATABASE command to take a database offline. Using this technique the database is still listed in that SQL Server but the underlying database files are unlocked and you can copy them. Similar to detaching a database, users cannot connect to it when it is offline. By taking the database offline, it still remains listed as a database on the server, but no one can connect to it.

Taking a database offline is not meant as a substitute for detaching a database and attaching it in another location. In normal SQL Server administration, you usually only take a database offline when you need to repair it, so it is not a good method for moving a database.

Using Database Backup and Restore

You can also use SQL Server’s native dataset backup and restore method to move a database. Like the attach and detach method, the backup and restore GUI and Transact-SQL commands support changing the file locations to the new SQL Server instance. In SQL Server 2005, the full-text catalog will be backed up automatically with the backup, and when you restore it you can specify the location of the full-text catalog, or leave it using the same paths as the old server provided the file structures on the new server are in place.

When you back up a database to a file, you are using the native SQL Server method for capturing everything in the database. To use this technique successfully you need to ensure that no users access the database after it is backed up. I’ll take a closer look at this problem in the next section.

After you’ve backed up the database to a disk file or to tape, you can then restore it to the new database location. During this time the old database is still online and the files are still locked by SQL Server. You can also use SQL Server 2005’s native checksum feature to validate the restored database for additional assurances that no corruption may have occurred during the backup process. The checksum validation, as well as automatic inclusion of full-text catalogs, are real advantages to the backup and restore method.

Controlling User Access

When you move a database from one server to another, or between SQL Server instances on the same server, you need to ensure that users are not able to access the old database before you start the move process. You can use several methods to accomplish this.

Most database administrators rely on those in control of the application to make sure that no users can connect to the database. In some cases, that may not be reliable and you may need to guarantee that yourself.

If you use the detach and attach method, after the detach, the database will not be visible and no users will be able to access the database to change any data. So this technique automatically blocks user access to the data when you detach a database. The database is simply not visible to the users.

A similar lack of access to the database occurs if you take a database offline. In this case, the database is listed by SQL Server as one of its databases, but users will receive an error if they try to access it.

Things are a little more complicated with backup and restore. By default, users can access the database even while you are backing it up. To prevent this, you start a query window in SQL Server 2005’s Management Studio or in SQL Server 2000’s Query Analyzer and alter the database so that only you can use it. You do this using the ALTER DATABASE command, setting the database SINGLE_USER, and removing all other users. Here’s a sample command:

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The ROLLBACK IMMEDIATE command removes all other users from the database and rolls back any of their open transactions. Then in the same query window, now issue your backup command. Here’s a sample of that command:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks.bak'

One problem here is that you need to keep your connection open so that you are the only user in the database. If you close the window and remove your connection, then exactly one other user might sneak in and connect.

If you want to ensure that no one else can enter the database, you still have some options. For example, you could make the database read only. However, you may not want anyone to even read the database. In that case, you could take one last transaction log backup and put the database into RECOVERY mode. 

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks_LastLog.trn'
WITH NORECOVERY

The catch-22 here is that you must do this from the master database, so you leave the database open for someone to get in there during the second or two it may take you to run the BACKUP LOG command. So this is not perfect.

Rolling Back the Move

With production data you can’t just move the database to a new location and be done with it. What if the move doesn’t succeed for some reason? I’ve seen cases where the application may not be able to access the database on the new location, and developers may not be able to detect and fix the cause. In that case, you may need to roll back the database move.

If you used the detach and attach technique and copied the files, your rollback plan could consist of just re-attaching the database to the original SQL Server instance. Therefore, don’t delete the files until you’re sure the new database is working properly!

Similarly, if the database is marked offline, you can simply take it back online.

If you are using the backup and restore method, the database is already online, but may be in single-user mode. You can just put it back into multi-user mode and your rollback is complete.

ALTER DATABASE AdventureWorks
SET MULTI_USER

No matter what method you choose, you need to provision for the possibility that you need to roll your move back.

Conclusion

I think the detach-attach and backup-restore are the best methods for moving a database. Each has advantages and disadvantages and you can make either method work. What’s critical is to make sure you provision for rolling back the move if something goes wrong!