Six ways to move data in SQL Server

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.

In fact, the establishment of the database, the establishment of foreign keys, 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 not recommended 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. Leveraging Backup and Recovery

A full backup of the source database is performed first. Back up to a device (device), then copy the backup file to the destination server (Fast recovery), restore the database, insert the name of the source database in the restored database name (the name must be the same), select the mandatory restore ( You can overwrite the options for the previous database, and when you choose to recover from the device, select the Backed-up file 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:

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:

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, However, if you use a remote linked server, the transfer performance between the network requires good, there are generally two kinds 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 (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 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:

The 1>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$ ....

The Windows NT account that 5> uses 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 replication is in another computer domain, you must establish a trust relationship between these domains. I am engaged in the work of the database administrator, to maintain a number of servers in the database, often a server in a database to move to another server, some experience in the movement of data, hoping to communicate with you