Six methods of data Movement for SQL Server 7.0 databases

Six methods of data Movement for SQL Server 7.0 databases
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.
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 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
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: