Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to migrate data in SQL Server. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

1. Import or export through the designer of the tool "DTS"

DTS designer powerful, support multitasking, is also a visual interface, easy to operate, but generally not many people know, if only part of the table in the SQL Server database to move, it is best to use this method, of course, you can also move all tables. In SQL Server Enterprise Manager, expand the + on the left side of the server, select the database, right-click, and select All tasks/Import Data... (or All tasks/Export Data...), enter the wizard mode and follow the instructions step by step, which is divided into very fine, flexible replication of data between different data sources, which is very convenient. And you can save it 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, which opens the DTS designer. It is worth noting that: if the table to be copied in the source database has a foreign key, pay attention to the order of movement, and sometimes move in batches, otherwise the primary key of the foreign key and the index may be lost, and the prompt next to the option is very clear when it is moved. or copy it to the target database at one time, and then re-establish the foreign key, primary key, index.

Note: in the establishment of the database, the establishment of foreign keys, primary keys, index files should be separated from the table files, and the data files should also be separated and placed on different drives, which is conducive to database optimization.

two。 Using "Bcp" tool

Although this tool is not recommended in versions of SQL Server7, 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, and secondly, it only copies between SQL Server tables (views) and text files, but its advantages are good performance, low overhead, low memory consumption and high speed. Interested friends can look up the reference manual.

3. Directly take advantage of backup and recovery

First of all, you need to make a full backup of the source database to a device (device), and then copy the backup text to the destination server (the recovery speed is fast), perform the database recovery operation, fill in the name of the source database in the restored database name (the name must be the same), select the mandatory restore (an option that can overwrite the previous data base), and choose to restore from the device. Just select the backed up file when browsing. This method can fully recover the database, including foreign keys, primary keys, and indexes.

4. Copy data files directly

Copy the data files (* .MDF) and log files (* .ldf) of the database to the destination server and restore them with statements in SQL Server Query Analyzer: the copy code is as follows: EXEC sp_attach_db @ dbname = 'test', @ filename1 =' d:mssql7data est_data.mdf', @ filename2 = 'd:mssql7data est_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: copy the code as follows: EXEC sp_detach_db @ dbname = 'test' EXEC sp_attach_single_file_db @ dbname =' test', @ physname = 'd:mssql7data est_data.mdf'

The purpose of this statement is to simply 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. Customize in the application

You can execute the program written by yourself in the application program (PB, VB) or in Query Analyzer. This method is more flexible. In fact, it uses a platform to connect to the database, and the SQL statement is mainly used in the platform. This method has little impact on the database, but if a remote link server is used, it requires good transmission performance between networks. Generally, there are two kinds of statements:

The copy code is as follows: 1 > select... Into new_tablename where... 2 > insert (into) old_tablename select... From... Where...

The difference is that the former inserts the data into a new table (first creating the table, then inserting the data), while the latter inserts the data into an existing table. I personally like the latter, because in terms of programming structure and scope of application, the second statement is stronger than the former.

Replication function of 6.SQL Server

SQL Server provides a powerful data replication feature, which is also the most difficult to master. For specific applications, please refer to the relevant materials. It is worth noting that some conditions are essential for successful data replication:

(1) SQL Server Agent must be started and MSDTC must be started.

(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, not 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 beyond this limit will fail.

(4) on the computer to be copied, it should be at least implied sharing, that is, the share name is C $or D $.

(5) the Windows NT account used for the SQL Server agent cannot be a local system account because the local system account does not allow network access.

(6) if the servers participating in the replication are in another computer domain, you need to establish a trust relationship between those domains.

The above is how to migrate data in SQL Server. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.