[SQL Server] database migration between SQL server computers

Note: Data can be migrated 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 the sp_attach_db system stored procedure or sp_attach_single_file_db system stored procedure, you can also 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 SQL Server Import and Export wizard (dtswizard.exe) (instead of the data import and export wizard of the data conversion service ).

 

 

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 specify the with norecovery option.

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 use the with recovery option to restore the transaction log backup to the target server. The stop time is limited to the transaction log backup and recovery time.

◆ 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.
◆ If you restore the database to a file location different from the source database file location, you must specify the with move option. 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, see related information.
◆ If you want to overwrite an existing database on the target server, you must specify the with replace option.
◆ 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 use the sp_detach_db and sp_attach_db stored procedures, follow these steps:

1. Use the sp_detach_db stored procedure to detach the database 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 files
. NDF	Auxiliary Data Files
. LDF	Transaction Log File

2. Use the sp_attach_db stored procedure to attach the database to the target server and point to the file that you copied to the target server in the previous step.

◆ The database cannot be accessed after the database is detached, 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 use an additional or separation method, the character set, sorting order, and Unicode Collation 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 move a database between two SQL Server 7.0 servers using backup and restoration or additional and separation methods, the character set, sorting order, and Unicode Collation 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 whole order of the target database will also be different from that of the master, model, tempdb, and MSDB databases of the target server.

Step 1: 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 need to be the same.
◆ Because unused space in the source database does not move, the target database does not need 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.
◆ SQL Server 7.0 data conversion service may fail to correctly transmit text and image data larger than 64 KB. However, this problem does not exist in the data conversion service of SQL Server 2000.

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. The default database for logon to the target server may be different from the default database for logon to the source server. You can use the sp_defaultdb stored procedure to change the default database to be logged on.

# P #

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 run the following code in your mobile user database:

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: the user or role '% s' already exists in the current database '. The preceding section describes how to use the sp_change_users_login stored procedure to correct isolated users one by one. The sp_change_users_login stored procedure can only address the isolated users of standard SQL Server logon accounts.

3. if the database owner (DBO) is listed as an isolated user, run the following code in the user database: exec sp_changedbowner 'sa 'This stored procedure changes the database owner to DBO and resolves this issue. To change the database owner to another user, run sp_changedbowner again with the user you want to use.

4. if your target server is running SQL Server 2000 Service Pack 1, after you perform additional or restore operations (or both, the list in the user folder of Enterprise Manager may not contain the database owner user.

5. If the target server does not have a DBO login mapped to the source server, you may receive the following error message when trying to change the system administrator (SA) password through the Enterprise Manager:

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.

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 expand the management folder.

2. Expand the SQL Server proxy, and right-click the alert, job, or operator.

3. Click all tasks and then click Generate SQL script. For SQL Server 7.0, click Generate scripts, alarms, or operators for all jobs.

You can right-click to generate scripts for all alarms, all jobs, or all operators.

◆ 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.

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. on the package menu, click Save.

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.

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 the MSDB system database. If you move the MSDB database, you do not have to perform steps 4th: how to move jobs, alarms, and operators, or 5th: how to move the DTS package ".