How to move databases between computers that are running SQL Server
The steps that are 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 that are not transferred when you follow the steps in this article, see the "More information" section.
Note For SQL Server 2008, go to the Managing Metadata When Making a Database Available on Another Server Instance Microsoft Developer Network (MSDN) website.
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.
Step 1: How to move user databases
If you are using SQL Server 2005, you can use the same method to migrate data from SQL Server 7.0 or from SQL Server 2000. However, the management tool in SQL Server 2005 differs from the management tool in SQL Server 7.0 or in SQL Server 2000. You should use SQL Server Management Studio instead of the SQL Server Enterprise Manager. Additionally, you should use the SQL Server Import and Export Wizard (DTSWizard.exe) instead of the Data Transformation Services Import and Export Data Wizard.
To move user databases, use one of the following methods.
Method 1: Back up and restore the user databases
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 complete, 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.
- Restore the full database backup on the destination server and specify the WITH NORECOVERY option.
Note To prevent additional database modifications, direct users to stop database activity on the source server.
- 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.
- Restore the full database backup on the destination server and specify the WITH NORECOVERY option.
- 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 Using the WITH MOVE option with the RESTORE statement304692 Moving SQL Server 7.0 databases to a new location with 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" later in this section.
Method 2: Use the sp_detach_db and sp_attach_db stored procedures
To use the sp_detach_db and sp_attach_db stored procedures, follow these steps:
- Detach the database on the source server by using the sp_detach_db stored procedure. You must copy the .mdf, .ndf, and .ldf files that are 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
- Attach the database on the destination server by using the sp_attach_db stored procedure and point to the files that 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 How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
- 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 following note about collation.
Method 3: Use the Import and Export Data Wizard to 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 do 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 be unable 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 be unable 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.
- Look for orphaned users. Open Query Analyzer on the destination server, and then run the following code in the user database that you moved: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.
exec sp_change_users_login 'Report'
- 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 How to resolve permission issues when you move a database between servers that are running SQL Server
The following Microsoft Knowledge Base 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.
- If the database owner (dbo) is listed as orphaned, run this code in the user database: The stored procedure changes the database owner to dbo and resolves the issue. To change the database owner to another user, run sp_changedbowner again with the user who you want. For more information, see the "sp_changedbowner" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
exec sp_changedbowner 'sa'
- 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
- 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 Cannot change SA password in Enterprise Manager
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:
- Open the SQL Server Enterprise Manager, and then expand the Management folder.
- Expand SQL Server Agent, and then right-click Alerts, Jobs, or Operators.
- Click All Tasks, and then click Generate SQL Script. For SQL Server 7.0, click Script All Jobs, Alerts, or Operators.
- 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 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: Save the DTS package on SQL Server
- Save the DTS package on the source server to a file, and then open the DTS package file on the destination server.
- 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: Open the DTS package in DTS Designer
- Open each DTS package in the DTS Designer.
- On the Package menu, click Save As.
- Specify the destination SQL Server.
Step 6: Change the sp_configure settings to match the previous computer
You may have to change the settings so that they match the settings in the new computer. For example, if the new computer has more memory or if it is running different SQL instances and applications, you may want to change the min and max server memory settings or the AWE setting. You may have to change the MAXDOP setting if the number of CPU cores that are exposed to the operating system has changed.
For more information about how to move full text components, click the following article number to view the article in the Microsoft Knowledge Base:
Article ID: 314546 - Last Review: 07/12/2013 07:34:00 - Revision: 12.1
- kbsqlsetup kbhowtomaster KB314546