Six data movement methods for SQL Server 7.0 database_PHP tutorial

Six data movement methods for SQL Server 7.0 database
I work as a database administrator. I need to maintain databases in multiple servers. I often move a database from one server to another server to modify the data. I have some experience in moving, and hope to share it with everyone.
1. Import or export through the designer of the tool DTS
The designer of DTS is powerful, supports multi-tasking, and is also a visual interface. It is easy to operate, but generally not many people know it. If you only work on SQL Server database This method is best for moving the middle table. Of course, all tables can also be moved. 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 wizard mode, and follow the prompts step by step. , which is very detailed and can flexibly copy data between different data sources, which is very convenient. And it can be saved as a DTS package. If you have the same copy task in the future, just run the DTS package directly, saving time and effort. You can also open the DTS designer directly by expanding Data Transformation Services under the server name, selecting Local Packages, right-clicking in the right window and selecting New Package to open the DTS designer. It is worth noting: If the table to be copied from the source database has foreign keys, pay attention to the order of movement. Sometimes it is necessary to move in batches, otherwise the foreign keys, primary keys, and indexes may be lost. The prompts next to the options when moving are very clear, or once Copy to the target database permanently, and then re-establish foreign keys, primary keys, and indexes.
In fact, when creating a database, the files used to create foreign keys, primary keys, and indexes should be separated from the table files, and the data files used should also be separated and placed on different drives, which is conducive to database optimization.
2. Use the Bcp tool
Although this tool is not recommended for use in SQL Server 7, 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. Secondly, it only copies between SQL Server tables (views) and text files. However, its advantages are good performance, low overhead, small memory usage, and fast speed. . Interested friends can check the reference manual.
3. Use backup and recovery
First make a full backup of the source database and back it up to a device, then copy the backup file to the destination server (recovery is fast) and perform database recovery operations. , fill in the name of the source database in the restored database name (the names must be the same), select forced recovery (the option of the previous database can be overwritten), select to restore from the device, and select the backed up file when browsing. This method can completely restore the database, including foreign keys, primary keys, and indexes.
4. Directly copy the data files
Copy the database data files (*.mdf) and log files (*.ldf) to the destination server, and use statements in SQL Server Query Analyzer to restore:

http://www.bkjia.com/PHPjc/631160.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631160.htmlTechArticleSix data movement methods for SQL Server 7.0 database. I work as a database administrator and maintain multiple servers. The database in a certain server often puts a certain database in a certain server...