How to move databases between computers running SQL Server

This page

Summary

If you are using SQL Server 2005

Backup and Restoration

Sp_detach_db and sp_attach_db stored procedures

Description of sorting rules

Import and export data (copying objects and data between SQL Server databases)

Step 2: Transfer logon and password

Step 2: How to isolate users

Step 2: How to move jobs, alarms, and operators

Step 2: how to move the DTS package

Method 1

Method 2

More information

Reference

Summary

This step-by-step section describes how to move Microsoft SQL Server user databases and most common SQL Server Components between computers running SQL Server.
The procedure described in this document assumes that you do not moveMaster,Model,TempdbOrMSDBThese System databases. These steps are for you to transfer logon andMasterAndMSDBMost common components in the database provide multiple options.
For information about specific projects that are not transferred when you perform the steps described in this article, see the "More Information" section in this article.
Note:: Supports data migration from SQL Server 2000 to Microsoft SQL Server 2000 (64-bit ). You can attach a 32-bit database to a 64-bit database by usingSp_attach_dbSystem stored procedures orSp_attach_single_file_dbSystem stored procedures, or use the backup and restoration functions in the 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, you cannot downgrade data from SQL Server 2000 (64-bit) to SQL Server 7.0. The following describes 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 management tools in Microsoft SQL Server 2005 are different from those in SQL Server 7.0 or SQL Server 2000. You should use SQL Server Management Studio (instead of the SQL Server Enterprise Manager) and the SQL Server Import and Export wizard (dtswizard.exe) (instead of the data conversion service import and export wizard ).

Backup and Restoration

Back up the user database on the source server and restore the user database to the target server.

• Someone may use the database during the backup process. If you execute the insert, update, or delete Statement on the database after the backup is complete, the changes are not included in the backup. If you have to transmit all the changes, you can transmit the changes in as short as possible if you have performed both transaction log backup and full database backup.

1. Restore the full database backup on the target server and specifyWith norecovery.
Note:: To prevent further modification to the database, instruct the user to exit the database activity on the source server.

2. execute the transaction log backup and then useWith recoveryOption to back up transaction logs to the target server. The stop time is limited to the transaction log backup and recovery time. For more information, see "Restore" subtopics in the "Transact-SQL reference" topic of SQL Server books online.

• 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 size of the source database before performing the backup, or compress the size of the target database after restoration. For more information, see the "compressed Database" subtopic under "Create and maintain database" in SQL Server books online.

• If you restore the database to a file location different from the source database file location, you must specifyWith move. For example, on the source server, the database is located in the D: \ MSSQL \ data folder. The target server does not have a drive D, so you need to restore the database to the C: \ MSSQL \ data folder. For more information about how to restore a database to another location, clickArticleTo view the articles in the Microsoft Knowledge Base:

221465 (http://support.microsoft.com/kb/221465/) INF: combined with the with move option and restore statement

304692 (http://support.microsoft.com/kb/304692/) INF: Move the SQL Server 7.0 database to a new location using backup and restore

• If You Want to overwrite an existing database on the target server, you must specifyWith replace. For more information, see "Restore" subtopics in the "Transact-SQL reference" topic of SQL Server books online.

• The character set, sorting order, and Unicode Collation on the source server and target server may be the same, depending on which version you want to restore to SQL Server. For more information, see the "about sorting rules" section in this article.

Sp_detach_db and sp_attach_db stored procedures

To useSp_detach_dbAndSp_attach_dbFollow these steps:

1. UseSp_detach_dbStored procedures are separated from databases on the source server. You must copy the. MDF,. NDF, And. LDF files associated with the database to the target server. See the description of the file type in the following table:

File Extension description

. MDF main data file

. NDF Auxiliary Data File

. LDF Transaction Log File

2. UseSp_attach_dbThe stored procedure attaches the database to the target server and points to the file that you copied to the target 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 (http://support.microsoft.com/kb/224071/) How to move an SQL Server database to a new location by using the separation and additional features in SQL Server

• After the database is detached, the database cannot be accessed and the database cannot be used when files are copied. At that moment of separation, all data contained in the database was moved.

• When you useAdditionalOrSeparationThe character set, sorting order, and Unicode order on both servers must be the same. For more information, see the "about sorting rules" section in this article.

Description of sorting rules

If you use backup and restore orAdditionalAndSeparationThe method moves the database between two SQL Server 7.0 servers. The character set, sorting order, and Unicode sorting order on both servers must be the same. If you move a database from SQL Server 7.0 to SQL Server 2000 or between different SQL Server 2000 servers, the database retains the full sequence of the source database. This means that if the order of the target server running SQL Server 2000 is different from that of the source database, the order of the target database will also be different from that of the target serverMaster,Model,TempdbAndMSDBThe database order is different. For more information, see the "hybrid whole-order environment" topic in SQL Server 2000 books online.

Import and export data (copying objects and data between SQL Server databases)

You can use the data conversion service import and export Wizard to copy the entire database or selectively copy objects and data from the source database to the target database.

• Someone may be using the source database during transmission. If someone is using the source database during transmission, you may see some blocking during transmission.

• When you use the import and export data wizard, the character set, sorting order, and sorting order of the source server and target server do not have to be the same.

• Because unused space in the source database does not move, the target database does not have to be as large as the source database. Similarly, if you only move some objects, the target database does not have to be as large as the source database.

• The SQL Server 7.0 data conversion service may fail to properly transmit text and image data larger than 64 KB. However, this problem does not exist in the data conversion service of SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

257425 (http://support.microsoft.com/kb/257425/) fix: DTS object transmission cannot transmit BLOB Data larger than 64 KB

Step 2: Transfer logon and password

If you do not transmit the login from the source server to the target server, the current SQL Server user cannot log on to the target server. You can transfer the logon and password as described in the following Microsoft Knowledge Base Article:

246133 (http://support.microsoft.com/kb/246133/) how to transfer login and password between SQL Server instances

The default database for logon to the target server may be different from the default database for logon to the source server. You can useSp_defaultdbStored Procedures to change the default database to be logged on. For more information, see the "sp_defaultdb" subtopic of the "Transact-SQL reference" topic in SQL Server books online.

Step 2: How to isolate users

After you transmit the login and password to the target server, the user may not be able to access the database. Logon is associated with the user by the security identifier (SID). After you move the database, if the SID is inconsistent, SQL Server may reject the user's access to the database. This issue is called isolated users. If you use the SQL Server 2000 DTS transmission login function to transmit the login and password, isolated users may be generated. In addition, access to the integrated Login Account of the target server in different domains with the source server may also lead to isolated users.

1. Search for isolated users. Open the query analyzer on the target server, and then run the followingCode:

 
Exec sp_change_users_login 'report'
 

This process lists any isolated users that are not linked to a Logon account. If no user is listed, skip steps 1 and 2 and proceed directly.

2. Solve isolated user problems. If a user is an isolated user, the database user can successfully log on to the server, but has no access to the database. If you attempt to grant logon access to the database, the following error message is displayed because the user already exists:

Microsoft SQL-DMO (ODBC sqlstate: 42000) Error 15023: a user or role '% s' already exists in the current database '.

For more information about how to solve isolated users, click the following article number to view the article in the Microsoft Knowledge Base:

240872 (http://support.microsoft.com/kb/240872/) How to address the permission issue when moving databases between servers running SQL Server

This article describes how to map logon to database users and how to resolve isolated users generated by standard SQL Server logon and integrated logon.

274188 (http://support.microsoft.com/kb/274188/) PRB: the "isolated user troubleshooting" topic in books online is incomplete

The above article introduces how to useSp_change_users_loginStored Procedures to correct isolated users one by one.Sp_change_users_loginStored procedures can only solve the isolated user problems of standard SQL Server login accounts.

3. If the database owner (DBOAre listed as isolated users. Run the following code in the user database:

 
Exec sp_changedbowner 'sa'
 

This stored procedure changes the database ownerDBOAnd solve this problem. To change the database owner to another user, run the command again with the user you want to use.Sp_changedbowner. For more information, see the "sp_changedbowner" subtopic in the "Transact-SQL reference" topic of SQL Server books online.

4. If your target server is running SQL Server 2000 Service Pack 1, the Enterprise ManagerUserThe list in the folder may not contain the database owner user. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

305711 (http://support.microsoft.com/kb/305711/) BUG: DBO users are not displayed in Enterprise Manager

5. If the target server does not have anyDBOYou are attempting to change the system administrator (SA) The password may receive the following error message:

Error 21776: [SQL-DMO] Name 'dbo' is not found in users collection. If the name is a valid name, use [] to separate different parts of the name, and then try again.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

218172 (http://support.microsoft.com/kb/218172/) PRB: SA password cannot be changed in Enterprise Manager

Warning: If you restore or attach a database again, the database user may be isolated again, so that you must repeat Step 1.

Step 2: How to move jobs, alarms, and operators

Step 2 is an optional operation. You can generate scripts for all jobs, alarms, and operators on the source server, and then run the scripts on the target server.

• To move jobs, alarms, and operators, follow these steps:

1. Open the SQL Server Enterprise Manager and expandManagementFolder.

2. ExpandSQL Server proxy, Right-clickAlert,JobOrOperator.

3. ClickAll tasksAnd then clickGenerate an SQL script. For SQL Server 7.0, clickGenerate scripts for all jobs,AlertOrOperator.

You can right-clickAll alarms,All jobsOrAll operatorsGenerate scripts.

• You can move jobs, alarms, and operators from SQL Server 7.0 to SQL Server 2000, or between running SQL Server 7.0 and running SQL Server 2000 computers.

• If sqlmail notification is set for the operator on the source server, sqlmail must be set on the target server to have the same function. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

263556 (http://support.microsoft.com/kb/263556/) INF: how to configure SQL mail

Step 2: how to move the DTS package

Step 2 is an optional operation. If the data transmission package is stored on the source server in SQL Server or in the database, you can move the package as needed. To move data transmission packets between servers, use one of the following methods.

Method 1

1. Save the DTS package to a file on the source server, and then open the DTS package file on the target server.

2. Save the packages on the target server to SQL Server or the repository.
Note:: You must use separate files to move these packages one by one.

Method 2

1. Open each DTS package in the DTs designer.

2. InPackageClickSave.

3. Specify the target SQL Server.

Note:: The package may not run properly on the new server. You may have to change the package to reference any connections, files, data sources, configuration files, and other information on the old source server to reference the new target server. You must make these changes one by one based on the design of each package.

More information

You may also need to move other projects, such as replication, log transfer, full-text directory, specified backup device, maintenance plan, and linked server. If necessary, you can check the source servers of these configurations and then manually set them on the target server.
For more information about how to move the full-text component, click the following article number to view the article in the Microsoft Knowledge Base:

240867 (http://support.microsoft.com/kb/240867/) how to move, copy, and back up full-text directory folders and files

The steps described in this article do not move the database relationship diagram and Backup and Restore History. If you must move this information, move itMSDBSystem database. How to moveMSDBFor information about databases, see the Microsoft Knowledge Base Article referenced in Step 1: How to move user databases. If you moveMSDBDatabase, you do not have to perform Step 1: How to move jobs, alarms, and operators, or step 2: how to move the DTS package ".

Reference

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

320125 (http://support.microsoft.com/kb/320125/) How to move database Diagrams

From: http://support.microsoft.com/kb/314546/zh-cn