Antonio Olmedo :: personal website

This document intends to define standard steps for moving a specific SQL Server database to a new server, optionally migrating from SQL Server 2000 to 2005 as well.

1. Different approaches of migration

Depending on the size of the database to be migrated and the downtime window of production server, different approaches can be used to move the database from the current server to a new one.

1.1. Attach/detach

If we can afford to bring the old server offline as much time as needed, we can use the attach/detach approach.

First of all, we need exclusive access to the database to migrate, let’s call it dbtomigrate. The best way to do it can be to set the database to single_user mode and wait until all connections get closed.

USE [master]
GO
ALTER DATABASE dbtomigrate
SET SINGLE_USER WITH ROLLBACK AFTER 60
GO

The query will finish its execution when there are no more connections to the database or when the time specified in the ROLLBACK AFTER parameter (in seconds) is over. Nonqualified transactions will be rolled back after 60 seconds in this example.

We need to know where the data and log files are physically stored before detaching the database:

USE [dbtomigrate]
GO
sp_helpfile
GO

This query will give us the filename of both data and log files.

Now, we can proceed with detaching the database:

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'dbtomigrate', @keepfulltextindexfile=N'true'
GO

Once the database is detached from the instance, the datafiles must be moved to their new location, using operating system tools and the information from sp_helpfile.

Re-attach the database in the new server, pointing to the new location of the files:

USE [master]
GO
sp_attach_db 'dbtomigrate','E:\DATA\dbtomigrate.mdf','F:\LOG\dbtomigrate.ldf'
GO

1.2. Backup/restore

If the system downtime needs to be reduced as much as possible, this approach is better although a bit trickier.

Most databases have (or should have) a maintenance plan setup that performs regular full and differential (optional) database backups followed by frequent transaction log backups. Figure 1 shows a remote database restore process that makes use of the full and differential backups only, making it applicable for all database recovery models.

Figure 1

Note the serial nature of the process and the sequence to be followed by each individual task. Steps 1 and 4 are tasks to perform against the current server, whereas steps 2, 5 and 6 are tasks to perform against the new server. Steps 3 and 7 are dependent on your system downtime policy, which may (in its simplest form) require all affected databases to be set in read-only mode.

So, perform a full database backup of dbtomigrate if there is not a backup already in the server. Then copy the file to the new server, if possible out of production hours to reduce impact on the network. Once the copy of the file is finished, restore the database in the new server with the NORECOVERY option.

You can use SQL Server Management Studio / Enterprise Manager to perform the backup and restore, or use T-SQL with these scripts as an example:

BACKUP DATABASE [dbtomigrate] TO  DISK = N'G:\dbtomigrate.bak' WITH NOFORMAT, NOINIT,  NAME = N'dbtomigrate', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [dbtomigrate] FROM  DISK = N'G:\dbtomigrate.bak' WITH  FILE = 1,  MOVE N'dbtomigrate' TO N'E:\SQL\DATA\dbtomigrate.mdf',  MOVE N'dbtomigrate_log' TO N'F:\SQL\TRANSATION\dbtomigrate.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Everything is ready for the system downtime, which can be done setting the database to read-only:

ALTER DATABASE dbtomigrate SET READ_ONLY

Now perform a differential backup, which will store the last changes of the database since the last full backup.

BACKUP DATABASE [dbtomigrate] TO  DISK = N'G:\dbtomigrate_dif.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'dbtomigrate', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Once finished, copy the file to the new server and restore it (with recovery): 

RESTORE DATABASE [dbtomigrate] FROM  DISK = N'G:\dbtomigrate_dif.bak' WITH  FILE = 1,  MOVE N'dbtomigrate' TO N'E:\SQL\DATA\dbtomigrate.mdf',  MOVE N'dbtomigrate_log' TO N'F:\SQL\TRANSATION\dbtomigrate.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

In the event that transaction log backups exist, then the approach would be to perform a NORECOVERY restore of the full, differential (if available) and all transaction logs (in the appropriate order) on the new server before the start of system downtime. Then after system downtime is initiated, perform a final transaction log backup on the old system and then restore it on the new server.

2. Other things to migrate

Depending on the versions of the old and new SQL Server, it might be needed to create/modify logins and users in the new system. You can do that with the wizards provided by Enterprise Manager / Management Studio.

Same thing can be done with jobs, alerts, and operators. Right click and generate scripts from the old system and execute these scripts in the new server.

Regarding DTS/SSIS, there are a lot of different possible situations that this document won’t cover. To move packages between same versions of SQL Server, it should be as easy as saving the package to file, copying the file to the new server and importing it. From SQL Server 2000 to 20005, the exportation of DTS packages can be more tedious and tricky but there are tools for executing or migrating DTS to SSIS like SQL Server 2005 Upgrade Advisor and Package Migration Wizard.

3. Conclusion

Your new server is ready to be put into production, so change your applications to point to the new server and good luck.

Here you have the word document defining the procedure.