Note: Support for data from SQL Server 2000 to migrate to Microsoft SQL Server 2000 (64 bit).You can attach a 32-bit database to a 64-bit database, the method is: use the system stored procedure or sp_attach_single_file_db sp_attach_db system stored procedure, or use 32-bit Enterprise Manager backup and restore functionality.You can SQL Server 32-bit and 64-bit move back and forth between the two versions of the database.You can also use the same method to migrate data from SQL Server 7.0.However, do not support the data from the SQL Server 2000 (64 bit) downgrade to SQL Server 7.0.These methods are described below.
If you are using SQL Server 2005
You can use the same method from SQL Server 7.0 or SQL Server 2000 migration data.However, Microsoft SQL Server 2005's management tools and SQL Server 7.0 or SQL Server 2000 management tools in the different.You should use SQL Server Management Studio (not the SQL Server Enterprise Manager) and SQL Server Import and Export Wizard (DTSWizard.exe) (rather than 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 destination server.When the backup process may have been using the database.If the user on the database after the backup INSERT, UPDATE, or DELETE statement, the backup will not include these changes.If you must transfer all the changes, then, if you only perform transaction log backup and perform a full database backup, you can stop time as short as possible to transmit these changes.
1. On the target server, restore the full database backup, and specify the WITH NORECOVERY option.
Note: To prevent further changes to the database, please instruct the user to exit the database activity on the source server.
2. Perform a transaction log backup, then use the WITH RECOVERY option to restore the transaction log backup to the destination server.Stop time is limited to the transaction log backup and recovery time.
◆ database on the target server database on the source server, the same size.To reduce the size of the database, you must perform a backup before the compressed size of the source database, or complete reduction in the size of the target database after compression.
◆ If you restore the database to the file location is different from the source database file location, you must specify the WITH MOVE option.For example, the source server, the database is located in D: MssqlData folder.Target server does not have D drive, so you need to restore the database to C: MssqlData folder.About how to restore the database to another location for more information, please see the relevant information.
◆ If you want to overwrite an existing target database on the server, you must specify the WITH REPLACE option.
◆ source server and destination server character set, sort order and Unicode collation may be the same, depending on how you want to revert to the kind of version of SQL Server.For more information, see the "Note on the collation" section.
Sp_detach_db and stored procedures Sp_attach_db
To use the sp_detach_db and sp_attach_db these two stored procedures, follow these steps:
1. Sp_detach_db stored procedure using the database on the source server separation.You must be associated with the database. Mdf,. Ndf, and. Ldf these three files to the destination server.See the table below in the description of the type of file:
File extension description
. Mdf main data file
. Ndf auxiliary data files
. Ldf transaction log file
2. Sp_attach_db stored procedure using the database attached to the target server, and point you in the previous step to the destination server's files.
◆ separate database will be unable to access the database, and can not copy the files to use the database.During the moment of separation of all the data contained in the database have been moved.
◆ When you use an additional or separate methods, both on the server character set, sort order and Unicode collation must be the same.For more information, see the "Note on the collation" section.
Note on the collation
If you use the backup and restore or attach, and separation of the two move between SQL Server 7.0 database server, both server character set, sort order and Unicode collation must be the same.If you move a database from SQL Server 7.0 SQL Server 2000, or in a different move between SQL Server 2000 database server, the database will retain the source database of the entire sequence.This means that if the target is running SQL Server 2000 server, the entire sequence of the entire sequence with the source database is different from the target database, the entire sequence will also work with the target server's master, model, tempdb and msdb database collation different.
The first step: Import and export data (in the replication between SQL Server database objects and data)
You can use Data Transformation Services Import and Export Data Wizard to copy the whole database or selectively to the source database objects and data copied to the target database.In the transmission process, some people may use the source database.If someone in the transmission process using the source database, you may see some blocking transmission of the phenomenon.
◆ When you use the Import and Export Data Wizard, the source server and target server's character set, sort order and collation not the same.
◆ because the source database unused space does not move, so the target database need not be as large as the source database.Similarly, if you move only some of the objects, the target database is also not as large as the source database.
◆ SQL Server 7.0 Data Transformation Services may not be correctly transmitted more than 64KB of text and image data.But the version of SQL Server 2000 Data Transformation Services is not present this problem.
Step 2: How to transfer logins and passwords
If you do not log on the source server to the target server, the current SQL Server users can not log on to the target server.Login on the destination server may be the default database on the source server's default database login different.You can use the sp_defaultdb stored procedure to change the default database login.
Step 3: How to resolve orphaned users
Transfer to the target server in your login and password, the user may not access the database.And the user is logged by the security identifier (SID) associated together; when you move the database, if the SID is inconsistent, SQL Server may deny the user access the database.This problem is known as an orphaned user.If you are using SQL Server 2000 DTS Transfer Login feature to transfer logins and passwords, it may have orphaned users.In addition, the source server are allowed to access the target server in a different domain login account integration will result in orphaned users.
1. Find orphaned users.In the Open Query Analyzer on the destination server, and then you move the user database, run the following code:
exec sp_change_users_login 'Report'
This process will list any account not linked to the isolation of a login user.If the user is not listed, skip Step 2 and Step 3, Step 4 directly.
2. To resolve orphaned users.If a user is isolated user, database user can successfully log on to the server, but not have access to the database.If you try to grant login access to the database, it will because the user already exists and the following error message:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 15023: The current database user or role already exists '% s'.The above describes how to use the sp_change_users_login stored procedure to correct the orphaned users one by one.sp_change_users_login stored procedure can only solve the standard SQL Server login account of the orphaned users.
3. If the database owner (dbo) are treated as isolated users listed in the user database, run the following code: exec sp_changedbowner 'sa' the stored procedure will change the database owner dbo and solve the problem.To change the database owner to another user, use the user you want to use again run sp_changedbowner.
4. If your target server is running SQL Server 2000 Service Pack 1, then when you perform additional operations or restore operations (or both operations are executed), the Enterprise Manager user folder in the list may not be a databaseowner of the user.
5. If the target does not exist on the server mapped to the dbo on the source server login, you try to change the system through the Enterprise Manager administrator (sa) password, you may receive the following error message:
Error 21776: [SQL-DMO] name 'dbo' was not found in the Users collection.If the name is a valid name, then use [] to separate different parts of the name, and then try again.
Warning: If you restore or attach the database again, the database user may again be isolated, so you must repeat step 3.
Step 4: How to move jobs, alerts and operators
Step 4 is optional.You can all work on the source server, alerts and operators generate scripts 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, then expand the Management folder.
2. Expand SQL Server Agent, and then right-click the alert, operation or operator.
3. Click All Tasks, then click Generate SQL Script.For SQL Server 7.0, click the Generate scripts for all jobs, alerts, or operators.
You can right-click options for all alerts, all jobs or all of the operators generate script.
◆ You can work, alerts and operators to move from SQL Server 7.0 SQL Server 2000, you can run SQL Server 7.0 and between computers running SQL Server 2000 Mobile.
◆ If the source server for the operator to set the SQLMail notice, the target server must also be set SQLMail, to have the same function.
Step 5: How to Move DTS Package
Step 5 is optional.If the DTS package on the source server, or stored in a SQL Server repository, you can move the packages when needed.To move the DTS packages between servers, use the following methods.
Method 1
1. DTS package on the source server will be saved to a file on the destination server and then open the DTS package file.
2. To save the package on the destination server to SQL Server or the repository.
Note: You must use a separate file to move the packages one by one.
Method 2
1. In the DTS Designer to open each DTS package.
2. In the package menu, click Save As.
3. Specify the target SQL Server.
Note: In the new server package may not run correctly.You may have to change the package, change any of the old package on the source server connection, files, data sources, configuration files, and other reference information to refer to the new target server.You must be designed according to each package to make these changes one by one package.
The steps described in this article does not move a database diagram and backup and restore history.If you must move the information, move the msdb system database.If you move the msdb database, you do not have to "Step 4: How to move jobs, alerts, and operators" or "Step 5: How to move the DTS package."