Several Methods for migrating data in SQL Server: SQL Server

Several Methods for migrating data in SQL Server: SQL Server

1. Import or export data using the "DTS" designer

The DTS designer is powerful and supports multiple tasks. 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, the prompt next to the option during the migration is very clear, or you can copy it to the target database at a time, and then re-create the foreign key, primary key, and index.

Note: 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. Direct backup and recovery

First, you need to perform a full backup of the source database, back up the source database to a device, and copy the backup file to the target server (fast recovery) for database restoration, fill in the name of the source database in the name of the recovered database (the name must be the same), select forced recovery (the options that can overwrite the previous data library), 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:
Copy codeThe Code is as follows:
EXEC sp_attach_db @ dbname = 'test ',
@ Filename1 = 'd: mssql7data est_data.mdf ',
@ Filename2 = 'd: mssql7data est_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:
Copy codeThe Code is as follows:
EXEC sp_detach_db @ dbname = 'test'
EXEC sp_attach_single_file_db @ dbname = 'test ',
@ Physname = 'd: mssql7data est_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. customization in applications

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 has little impact on the database. However, if a remote connection server is used, the transmission performance between networks is required. Generally, there are two types of statements:

Copy codeThe Code is as follows:
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 a text or image data type, 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, you need to establish a trust relationship between these domains.

Sqlserver2000 database data transfer

To ensure that the structure and relationship of the original table remain unchanged, it is best to use the method of Detach or backup restoration for migration.

Help: migrate data between SQL Server databases

What does table difference mean that the primary key is also different?
If you know nothing about the database, you 'd better not do it easily. It involves too many problems !~

At the very least, you need to investigate the relationship between the previous table and the current table, and then relay the database according to the corresponding field, as well as the previous view function type procedure and so on !~
We recommend that you find someone you are familiar !~