Six data migration methods for SQL Server

1. Import or export data using the DTs designer

The DTS designer is powerful and supports multi-task operations. It is also a visual interface and easy to operate. However, there are not many people who know it. If you only want to move the table shards in the SQL Server database, it is best to use this method, of course, you can also move all tables. In SQL Server Enterprise Manager, expand + on the left of the server, select database, right-click, and select all tasks/import data... (or all tasks/export data ...), enter the wizard mode and follow the prompts to copy data between different data sources. In addition, the data transmission package can be saved as another data transmission package. If you have the same copy task in the future, you can directly run the data transmission package, saving time and effort. You can also directly open the DTs designer by expanding data transformation services under the server name, selecting local packages, right-clicking in the window on the right, and selecting new package, the DTS designer is enabled. It is worth noting that if the table to be copied from the source database has a foreign key, pay attention to the moving order, and sometimes move it in batches. Otherwise, the foreign key primary key may be lost, when moving, the prompt next to the option is very clear, or one-time copy to the target database, and then re-establish the foreign key, primary key, and index.

In fact, when creating a database, the files with foreign keys, primary keys, and indexes should be separated from the table creation files, and the data files should be separated and placed on different drives respectively, it is conducive to database optimization.

2. Use the BCP Tool

Although this tool is not recommended in SQL Server 7, many database administrators still like it, especially those who have used an earlier version of SQL Server. BCP has limitations. First, its interface is not graphical. Second, it only copies between SQL Server tables (Views) and text files. However, BCP has good performance, low overhead, low memory usage, and fast speed. If you are interested, you can check the reference manual.

3. backup and recovery

Back up the source database to a device, copy the backup file to the target server (fast recovery), and restore the database, enter the name of the source database in the name of the recovered database (the name must be the same), select forced recovery (the options of the previous database can be overwritten), and choose to recover from the device, select the backup file during browsing. This method completely restores the database, including foreign keys, primary keys, and indexes.

4. Directly copy data files

Copy the data files (*. MDF) and log files (*. LDF) of the database to the target server and use the statement in SQL Server Query analyzer for recovery:

Exec sp_attach_db @ dbname = 'test ',
@ Filename1 = 'd: \ MSSQL7 \ data \ test_data.mdf ',
@ Filename2 = 'd: \ MSSQL7 \ data \ test_log.ldf'
In this way, the test database is appended to SQL Server and can be used as usual. If you do not want to use the original log file, use the following command:
Exec sp_detach_db @ dbname = 'test'
Exec sp_attach_single_file_db @ dbname = 'test ',
@ Physname = 'd: \ MSSQL7 \ data \ test_data.mdf'
This statement only loads data files. log files can be automatically added by the SQL Server database, but the data recorded in the original log files is lost.

5. ApplicationProgramMedium Customization

You can execute your own programs in the application (Pb, VB) or in query analyzer. This method is flexible. In fact, you can use a platform to connect to the database, the main SQL statement used on the platform, which has little impact on the database. However, if a remote connection server is used, the transmission performance between networks is required to be good. Generally, there are two types of statements:
1> Select... into new_tablename where...
2> insert (into) old_tablename Select... from... where...
The difference is that the former inserts data into a new table (create a table first and then insert data), and the latter inserts data into an existing table. I personally like the latter, because in terms of programming structure and application scope, the second statement is better than the former statement.

6. SQL Server replication

SQL Server provides powerful data replication functions, which are also the most difficult to master. For specific applications, please refer to the relevant materials. It is worth noting that to successfully copy data, some conditions are indispensable:
1> the SQL Server Agent must be started, and MSDTC must be started.
2> all tables to be copied must have primary keys.
3> If the table has text or image data types, you must use the with log option. You cannot use the with no_log option.
In addition, the max text repl Size Option controls the maximum size of text and image data that can be copied. Operations that exceed this limit will fail.
4> on the computer to be copied, there should be at least implicit sharing, that is, the sharing name is C $ or d $ ....
5> the Windows NT account used by the SQL Server proxy cannot be a local system account because the local system account does not allow network access.
6> If the server involved in replication is in another computer domain, a trust relationship must be established between these domains. I am working as a database administrator. To maintain databases on multiple servers, I often move a database on one server to another. I have some experience in moving data, I hope to share with you.