1. Import or export data using the DTS designer
The DTS designer is powerful and supports multi-task operations. It is also a visual interface and easy to operate. However, there are not many people who know it. If you only want to move the table shards in the SQL Server database, it is best to use this method, of course, you can also move all tables. In SQL Server Enterprise Manager, expand on the left of the Server, select database, right-click, and select All tasks/Import Data... (or All tasks/Export Data ...), enter the wizard mode and follow the prompts to copy data between different data sources. In addition, the data transmission package can be saved as another data transmission package. If you have the same copy task in the future, you can directly run the data transmission package, saving time and effort. You can also directly open the DTS designer by expanding Data Transformation Services under the server name, selecting Local Packages, right-clicking in the window on the right, and selecting New Package, the DTS designer is enabled. It is worth noting that if the table to be copied from the source database has a foreign key, pay attention to the moving order, and sometimes move it in batches. Otherwise, the foreign key primary key may be lost, when moving, the prompt next to the option is very clear, or one-time copy to the target database, and then re-establish the foreign key, primary key, and index.
In fact, when creating a database, the files with foreign keys, primary keys, and indexes should be separated from the table creation files, and the data files should be separated and placed on different drives respectively, it is conducive to database optimization.
2. Use the Bcp tool
Although this tool is not recommended in SQL Server 7, many database administrators still like it, especially those who have used an earlier version of SQL Server. Bcp has limitations. First, its interface is not Graphical. Second, it only copies between SQL Server Tables (views) and text files. However, Bcp has good performance, low overhead, low memory usage, and fast speed. If you are interested, you can check the reference manual.
3. Backup and recovery
Back up the source database to a device, copy the backup file to the target server (fast recovery), and restore the database, enter the name of the source database in the name of the recovered database (the name must be the same), select forced recovery (the options of the previous database can be overwritten), and choose to recover from the device, select the backup file during browsing. This method completely restores the database, including foreign keys, primary keys, and indexes.
4. Directly copy data files
Copy the data files (*. mdf) and log files (*. ldf) of the database to the target Server and use the statement in SQL Server Query Analyzer for recovery:
EXEC sp_attach_db @ dbname = 'test ',
@ Filename1 = 'd: mssql7dataest_data.mdf ',
@ Filename2 = 'd: mssql7dataest_log.ldf'
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
