Several ways to migrate data in SQL Server _mssql

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

DTS's designer features a powerful, multitasking, visual interface, easy to operate, but not many people know, if only for the SQL Server database to move some of the tables, this method is best, of course, you can also carry out 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 walk on the line, the inside is very fine, you can flexibly copy data between different sources, very convenient. And can be saved as a DTS package, if you have the same replication tasks, directly run DTS package on the line, save time and effort. You can also open DTS Designer directly by expanding the data transformation Services under the server name, selecting Local Packages, right-clicking in the right window, selecting new Package, and opening DTS Designer. It is worth noting that if the source database is to be copied from a table with a foreign key, pay attention to the order of movement, sometimes to move in batches, or foreign key primary key, index may be lost, move the option next to the hint said very clearly, or one-time copy to the target database, and then re-establish foreign keys, primary keys, index.

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

2. Using the "BCP" tool

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

3. Direct use of backup and recovery

You first need to make a full backup of the source database, back up to a device (device), and then copy the backup file to the destination server (Fast recovery), restore the database, and insert the name of the source database in the restored database name (the name must be the same). Select Forced recovery (you can overwrite the previous database options), select Restore from Device, and select backed up files when browsing. This method can completely restore the database, including foreign keys, primary keys, and indexes.

4. Direct copy of data files

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

Copy Code code 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 Code code as follows:

EXEC sp_detach_db @dbname = ' Test '
EXEC sp_attach_single_file_db @dbname = ' Test ',
@physname = ' D:mssql7data est_data.mdf '

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

5. Customizing in the Application

You can execute your own program in the application (PB, VB), or you can execute it in Query Analyzer, this method is more flexible, in fact, the use of a platform to connect to the database, in the platform of the main time SQL statements, this method of the impact on the database is small, but if used far Cheng connect the server, require the transmission between the network performance is good, generally have two kinds of statements:

Copy Code code 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 (creating a table, then inserting data), which inserts data into a table that already exists, and I personally like the latter, because the second statement is stronger than the former in terms of the programmatic structure of the application.

6.SQL server's replication capabilities

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

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

(2) All tables to be replicated 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 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, and operations that exceed this limit will fail.

(4) on the computer to be replicated, it should be at least implicitly shared, that is, the share name is C $ or d$ ....

(5) The Windows NT account used for the 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 another computer domain, you need to establish a trust relationship between those domains.