1. Import or export by tool DTS designer
The DTS designer is powerful, supports multitasking, is also a visual interface, easy to operate, but knowing people generally do not have much, if only the movement of partial tables in the SQL Server database, the best, of course, can also be carried out All table moves. 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 prompt step by step It is very convenient to replicate data between different data sources, it is very convenient. Moreover, it can be other than DTS packets, and if there is the same copy task in the future, the DTS package is run directly, saving time and effort. You can also open the DTS designer directly, the method is to expand the Data Transformation Services below the server name, select Local Packages, right click on the window, select New Package, open the DTS designer. It is worth noting that if the source database is to be copied, the order of movement, pay attention to the order of movement, sometimes moving batch, otherwise the key key key, the index may be lost, the prompt next to the option is very clear, or once Sexual copying to the target database, re-establish the foreign key, primary key, index.
When it is actually established a database, the files that have been established, the primary key, and the index should be separated from the construction form file, and the data files are also separated and placed on different drives, which facilitates the optimization of the database.
2. Utilize BCP Tools
Although this tool is not recommended in SQL Server7 versions, many database administrators still like to use it, especially those who have used SQL Server's earlier versions. BCP has limitations. First, its interface is not graphical, followed by copying between SQL Server's table (view) and text files, but its advantages are good performance, small overhead, less memory, less memory, speed fast . Interested friends can check the reference manual.
3. Use backup and recovery
Firstly back up the source database, back up to a device (Device), then copy the backup file to the destination server (fast fast), perform the recovery operation of the database, fill in the source database in the recovered database name Name (the name must be the same), select Forced Recovery (option to overwrite the previous database option), select the file in the selection, check the backup files when browsing. This method can completely restore the database, including foreign keys, primary keys, indexes.
4. Direct copy data file
Copy the database's data file (* .mdf) and log file (* .ldf) to the destination server, recover with statements in SQL Server Query Analyzer:
EXEC SP_ATTACH_DB @dbname = 'Test',
@ filename1 = 'd: /msql7/data/test_data.mdf',
@ filename2 = 'd: /msql7/data/test_log.ldf'
This attaches the Test database to the SQL Server and can be used as usual. If you don't 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: /msql7/data/test_data.mdf'
The role of this statement is to load only data files, and the log file can be added automatically by the SQL Server database, but the data recorded in the original log file is lost.
5. Customize in the application
You can perform your own program in the application (PB, VB), or in Query Analyzer, this method is flexible, in fact, using a platform to connect to the database, in the platform, the main SQL statement, this The method affects the database, but if the remote link server is used, it is required to have a good transmission performance between the network, and generally there are two statements:
1> SELECT ... INTO New_TABLENAME WHERE ...
2> INSERT (INTO) OLD_TABLENAME SELECT ...WHER ...
The difference is that the former inserts the data into a new table (first set the table, then insert data), the latter is inserting the data into a table that already exists, I personally like the latter, because of the structure, the application range, The second statement is stronger than the former.
6. SQL Server replication function
SQL Server provides a powerful data replication feature, which is also the least easy to master. Please refer to relevant information for specific applications. It is worth noting that you want to successfully perform data copy work, some conditions are essential:
1> SQL Server Agent must start, the MSDTC must start.
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 you cannot use the with no_log option.
In addition, the Max TEXT REPL SIZE option controls the maximum size of the text and image data that can be copied, and the operation that exceeds this limit will fail.
4> On the computer to be copied, it should be at least hidden sharing, ie the shared name is C $ or D $ ....
5> Windows NT accounts used for SQL Server agents cannot be a local system account because local system accounts do not allow network access.
6> If you participate in the copy server in another computer domain, you must establish a trust relationship between these domains.
