Several ways to migrate data in SQL Server

1. Import or export through the designer of the tool "DTS"

DTS's designer is powerful, multi-tasking, and visual interface, easy to operate, but people generally do not know much, if only a few tables in the SQL Server database movement, it is best to use this method, of course, can also be all the table movement. In SQL Server Enterprise Manager, expand the + on the left side of the server, select the database, right-click, select All Tasks/import Data ... (or all tasks/export data ...), enter the wizard mode, follow the prompts step by step to go on the line, the inside is very thin, can be flexible in different data sources to replicate data, very convenient. And can be saved as a DTS package, if you have the same replication task, run the DTS package directly, save time and effort. You can also open DTS Designer directly by expanding data transformation Services under the server name, selecting local Packages, right-clicking in the right-hand window, and selecting new package to open DTS Designer. Noteworthy is: If the source database to copy the table has foreign keys, note the order of movement, and sometimes to move in batches, otherwise the foreign key primary key, the index may be lost, moving when the prompt next to the message is very clear, or a one-time copy to the target database, and then re-establish the foreign key, primary key, index.

Note: In the establishment of the database, the establishment of the foreign key, the primary key, the index of the file should be separated from the build table file, and the data files are separated, and placed on different drives, to facilitate the database optimization.

2. Using the BCP tool

Although this tool is deprecated in the version of SQL Server7, many database administrators still like to use it, especially those who have used earlier versions of SQL Server. BCP has limitations, first of all its interface is not graphical, and secondly it is only in the SQL Server table (view) and text file replication between, but it has the advantage of good performance, low overhead, memory consumption, fast. Interested friends can check the reference manual.

3. Direct use of backup and recovery

First, a full backup of the source database, backup to a device, and then copy the backup files to the destination server (Fast recovery), the database recovery operations, in the restored database name to fill in the name of the source database (the name must be the same), Select Mandatory recovery (the option to overwrite the previous database), select Restore from the device, and select the backed up file when browsing. This method can fully restore the database, including foreign keys, primary keys, indexes.

4. Copying data files directly

Copy the database data files (*.mdf) and log files (*.ldf) to the destination server and use the statements in SQL Server Query Analyzer to recover:

Copy CodeThe code is as follows:
EXEC sp_attach_db @dbname = ' Test ',
@filename1 = ' D:mssql7data est_data.mdf ',
@filename2 = ' D:mssql7data est_log.ldf '

This attaches the test database to SQL Server and can be used as usual. If you do not want to use the original log file, you can 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 '

The purpose of this statement is to load only the data file, the log file can be added automatically by the SQL Server database, but the data recorded in the original log file is lost.

5. Customizing in the Application

Can be in the application (PB, VB) to execute their own programs, can also be executed in Query Analyzer, this method is more flexible, in fact, the use of a platform to connect to the database, in the platform with the main SQL statement, this method has little impact on the database, but if the use of far Subroutines server, requires a good transmission between the network, there are generally two kinds of statements:

Copy CodeThe code is as follows:
1>select ... into new_tablename where ...
2>insert (INTO) old_tablename Select ... from ...

The difference is that the former inserts the data into a new table (creating a table before inserting the data), the latter is inserting the data into a table that already exists, I personally like the latter, because in the programming structure, the scope of application, the second statement is stronger than the former.

Replication capabilities for 6.SQL server

SQL Server provides powerful data replication capabilities, but also the most difficult to grasp, specific applications please refer to the relevant information, it is worth noting that in order to successfully replicate the data, some conditions are essential:

(1) The SQL Server agent must be started and MSDTC must be started.

(2) All tables to be copied must have a primary key.

(3) If there is a text or image data type in the table, you must use the WITH LOG option and 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, and operations that exceed this limit will fail.

(4) on the computer to be copied, it should be at least an implicit share, that is, the share name is C $ or d$ ....

(5) The Windows NT account used for SQL Server Agent cannot be a local system account because the local System account does not allow network access.

(6) If the server participating in the replication is in a different computer domain, you need to establish a trust relationship between those domains.

Several ways to migrate data in SQL Server