How to move databases between computers that are running SQL Server (314546)

SUMMARY

This step-by-step article describes how to move Microsoft SQL Server user databases and most common SQL Server components between computers that are running SQL Server.

The steps described in this article assume that you will not move the master, model, tempdb, or msdb system databases. The steps provide different options for you to transfer logins and the most common components that are contained in the master and msdb databases.

For information about the specific items not transferred when you follow the steps in this article, see the "More Information" section of this article.

Note Data migration from SQL Server 2000 to Microsoft SQL Server 2000 (64-bit) is supported. You can attach a 32-bit database to a 64-bit database by using the sp_attach_db system stored procedure or the sp_attach_single_file_db system stored procedure, or by using backup and restore in the 32-bit Enterprise Manager. You can move databases back and forth between the 32-bit and the 64-bit versions of SQL Server. You can also migrate data from SQL Server 7.0 by using the same methods. However, downgrading data to SQL Server 7.0 from SQL Server 2000 (64-bit) is not supported. A description of each method follows.

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 management tool in Microsoft SQL Server 2005 differs from the management tool in SQL Server 7.0 or in SQL Server 2000. You should use the SQL Server Management Studio instead of the SQL Server Enterprise Manager, 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 databases on the source server, and then restore the user databases to the destination server.

• The database can be used when the backup is in process. If users perform INSERT, UPDATE, or DELETE statements on the database after the backup is completed, the backup will not contain these changes. If you must transfer all changes, you can transfer the changes with minimal downtime if you perform both a transaction log backup and a full database backup.
  1. Restore the full database backup on the destination server and specify the WITH NORECOVERY option.
     
     Note To prevent additional database modifications, direct users to quit database activity on the source server.
  2. Perform a transaction log backup and restore the transaction log backup to the destination server by using the WITH RECOVERY option. Downtime is limited to the time of the transaction log backup and restore. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
• The database on the destination server will be the same size as the database on the source server. To reduce the size of the database, you must either reduce the source database before you perform the backup, or reduce the destination database after the restore is completed. For more information, see the "Shrinking a Database" sub-topic in the "Creating and Maintaining Databases" heading of SQL Server Books Online.
• 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 in the D:\Mssql\Data folder. The destination server does not have a D drive, and you want to restore the database to the C:\Mssql\Data folder. For more information about how to restore a database to a different location, click the following article numbers to view the articles in the Microsoft Knowledge Base:

  221465 INF: Using the WITH MOVE option with the RESTORE statement

  304692 INF: Moving SQL Server databases to a new location by using BACKUP and RESTORE

• If you want to overwrite a pre-existing database on the destination server, you must specify the WITH REPLACE option. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
• Depending on the version of SQL Server to which you restore, the character set, sort order, and Unicode collation may have to be the same on both the source and destination servers. For more information, see the "Note About Collation" section of this article.

Sp_detach_db and Sp_attach_db stored procedures

To use the sp_detach_db and sp_attach_db stored procedures, follow these steps:

1. Detach the database on the source server by using the sp_detach_db stored procedure. You must copy the .mdf, .ndf and .ldf files associated with the database to the destination server. See this table for a description of the file types:

   File name extension	Description
   .mdf	Primary data file
   .ndf	Secondary data file
   .ldf	Transaction log file

2. Attach the database on the destination server by using the sp_attach_db stored procedure and point to the files you copied to the destination server in the previous step. For more information about how to use these methods, click the following article number to view the article in the Microsoft Knowledge Base:

   224071 INF: Moving SQL Server databases to a new location by using Detach/Attach

• The database is inaccessible after the detach and you cannot use the database when you copy the files. All data that is contained in the database at the point in time of the detach is moved.
• The character set, sort order, and Unicode collation may have to be the same on both servers when you use the Attach or Detach method. For more information, see the "Note About Collation" section of this article.

Note about collation

If you move databases between SQL Server 7.0 servers by using the backup and restore or Attach and Detach methods, the character set, sort order and Unicode collation must be the same on both servers. If you move databases from SQL Server 7.0 to SQL Server 2000 or between SQL Server 2000 servers, the database retains the collation of the source database. This means that if the destination server that is running SQL Server 2000 has a different collation than the source database, the destination database has a different collation than the destination server's master, model, tempdb and msdb databases. For more information, see the "Mixed Collation Environments" topic in SQL Server 2000 Books Online.

Import and Export data (copy objects and data between SQL Server databases)

You can copy a whole database or selectively copy objects and data from the source database to the destination database by using the Data Transformation Services Import and Export Data Wizard.

• The source database may be used during the transfer. If the source database is used during the transfer, you may see some blocking when the transfer is in progress.
• When you use the Import and Export Data Wizard, the character set, sort order and collation does not have to be the same between the source server and destination server.
• Because unused space in the source database does not move, the destination database may not have to be as large as the source database. Similarly, if you move only some objects, the destination database may not have to be as large as the source database.
• SQL Server 7.0 Data Transformation Services may not transfer text and image data longer than 64 KB correctly. This problem does not apply to the SQL Server 2000 version of Data Transformation Services. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

  257425 FIX: DTS Object Transfer does not transfer BLOB data greater than 64 KB

Step 2: How to transfer logins and passwords

If you do not transfer the logins from the source server to the destination server, your current SQL Server users may not be able to log on to the destination server. You can transfer the logins and passwords by using the instructions in the following Microsoft Knowledge Base article:

Step 3: How to resolve orphaned users

After you transfer logins and passwords to the destination server, users may not be able to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. This problem is known as an orphaned user. If you transfer logins and passwords by using the SQL Server 2000 DTS Transfer Login feature, you will probably have orphaned users. Additionally, integrated logins granted access on a destination server in a different domain than the source server cause orphaned users.

1. Look for orphaned users. Open Query Analyzer on the destination server, and then run the following code in the user database you moved:

   exec sp_change_users_login 'Report'

   The procedure lists any orphaned users who do not link to a logon. If no users are listed, skip step 2 and step 3 and go to step 4.
2. Resolve the orphaned users. If a user is orphaned, database users can log on to the server successfully but will not have permission to access the database. If you try to grant the logon access to the database, you receive the following error message because the user already exists:
   Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
   For more information about how to resolve orphaned users, click the following article numbers to view the articles in the Microsoft Knowledge Base:

   240872 INF: How to resolve permission issues when a database is moved between SQL Servers

   
   This article contains instructions about how to map the logins to the database users and resolves users orphaned from standard SQL Server logins and integrated logins.
   
   

   274188 "Troubleshooting Orphaned Users" topic in Books Online is incomplete

   
   This article describes how to use the sp_change_users_login stored procedure to correct the orphaned users one by one. The sp_change_users_login stored procedure only resolves users orphaned from standard SQL Server logins.
3. If the database owner (dbo) is listed as orphaned, run this code in the user database:

   exec sp_changedbowner 'sa'

   The stored procedure changes the database owner to dbo and corrects the issue. To change the database owner to another user, run sp_changedbowner again with the user you want. For more information, see the "sp_changedbowner" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
4. If your destination server is running SQL Server 2000 Service Pack 1, the database owner user may not be in the list in the Users folder in Enterprise Manager after you perform the attach or restore or both. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

   305711 BUG: DBO user does not display in Enterprise Manager

5. You may receive the following error message if you try to change the system administrator (sa) password through Enterprise Manager if the logon that was mapped to dbo on the source server does not exist on the destination server:
   Error 21776: [SQL-DMO] The name 'dbo' was not found in the Users collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:

   218172 PRB: Cannot change SA password in Enterprise Manager

Warning If you restore or attach the database again, the database users may be re-orphaned and you have to repeat step 3.

Step 4: How to Move jobs, alerts and operators

Step 4 is optional. You can generate scripts for all jobs, alerts and operators on the source server, and then run the script on the destination server.

• To move jobs, alerts and operators, follow these steps:
  1. Open the SQL Server Enterprise Manager, and then expand the Management folder.
  2. Expand SQL Server Agent, and then either right-click Alerts, Jobs, or Operators.
  3. Click All Tasks, and then click Generate SQL Script. For SQL Server 7.0, click Script All Jobs, or Alerts, or Operators.
  You will have the option to generate scripts for All Alerts, All Jobs or All Operators based on the item you right-click.
• You can move jobs, alerts and operators from SQL Server 7.0 to SQL Server 2000 or between computer servers that are running SQL Server 7.0 and SQL Server 2000.
• If you have operators that are set up for notification by SQLMail on the source server, you have to set up SQLMail on the destination server to have the same functionality. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

  263556 INF: How to configure SQL Mail

Step 5: How to move DTS packages

Step 5 is optional. If DTS packages are stored on the source server in the SQL Server or the repository, you can move them if you want. To move DTS packages between servers, use one of the following methods.

Method 1

1. Save the DTS package on the source server to a file, and then open the DTS package file on the destination server.
2. Save the package on the destination server to the SQL Server, or to the repository.
   Note You have to move each package one by one in separate files.

Method 2

1. Open each DTS package in the DTS Designer.
2. On the Package menu, click Save As.
3. Specify the destination SQL Server.

Note The package may not run correctly on the new server. You may have to change the package, and change any references in the package to connections, files, data sources, profiles and other information located on the old source server, to reference the new destination server. You must make these changes on a package by package basis based on the design of each package.