Moving a database between computers running SQL Server

This step-by-step article describes how to move the Microsoft SQL Server user database and most common SQL Server components between computers running SQL Server. The steps that are described in this article assume that you do not move the system databases of master, model, tempdb, or msdb. These steps provide you with multiple options for transferring logins and most of the common components that are contained in the master and msdb databases.

Note: Support for migrating data from SQL Server 2000 to Microsoft SQL Server 2000 (64-bit). You can attach a 32-bit database to a 64-bit database by using sp_attach_db system stored procedures or sp_attach_single_file_db system stored procedures, or by using the backup and restore features in 32-bit Enterprise Manager. You can move the database back and forth between the 32-bit and 64-bit versions of SQL Server. You can also use the same method to migrate data from SQL Server 7.0. However, it is not supported to demote data from SQL Server 2000 (64-bit) to SQL Server 7.0. Here are some of these methods.

If you are using SQL Server 2005

You can use the same method to migrate data from SQL Server 7.0 or SQL Server 2000. However, the administrative tools in Microsoft SQL Server 2005 are different from the administrative tools in SQL Server 7.0 or SQL Server 2000. You should use SQL Server Management Studio (instead of SQL Server Enterprise Manager) and the SQL Server Import and Export Wizard (DTSWizard.exe) instead of the Data Transformation Services import and Export Data Wizard.

Backup and restore

Back up the user database on the source server, and then restore the user database to the target server. A database may be used during the backup process. If the user performs an INSERT, UPDATE, or DELETE statement on the database after the backup completes, the changes are not included in the backup. If you have to transfer all the changes, if you perform both transaction log backups and full database backups, you can transfer those changes with the shortest possible stop time.

1. Restore the full database backup on the target server and specify the WITH NORECOVERY option.

Note: To prevent further modifications to the database, instruct the user to exit the database activity on the source server.

2. Perform transaction log backups, and then use the WITH RECOVERY option to restore transaction log backups to the target server. The stop time is limited to the time of transaction log backup and recovery.

The database on the target server will be the same size as the database on the source server. To reduce the size of the database, you must compress the source database before performing the backup, or compress the target database size after the restore is complete.

If you restore the database to a different file location than the source database, you must specify the WITH MOVE option. For example, on the source server, the database is located in the D:\Mssql\Data folder. The destination server does not have a D drive, so you need to restore the database to the C:\Mssql\Data folder. For more information about how to restore the database to a different location, see the relevant information.

If you want to overwrite an existing database on the target server, you must specify the WITH REPLACE option.

The character set, sort order, and Unicode integer on the source and destination servers may have to be the same, depending on which version of the SQL Server you are restoring to. For more information, see the "Description of Collations" section of this article.