Six data migration methods for SQL Server databases

1. the tool DTS designer is powerful in importing or exporting data transmission. It supports multiple tasks and is also a visual interface that is easy to operate, but there are not many people who know it, if you only want to move the sub-tables in the middle of the SQL Server database, it is best to use this method. Of course, you can also move all the 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. Although this tool is not recommended in SQL Server 7, many database administrators still like it, especially those who have used earlier SQL Server versions. 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. back up and recover the source database completely, back up the database to a device, and copy the backup file to the target server (fast recovery ), to restore the database, enter the name of the source database in the name of the recovered database (the name must be the same), and select forced recovery (the options of the previous database can be overwritten ), when you select to recover from the device, you can select the backup file during browsing. This method completely restores the database, including foreign keys, primary keys, and indexes. 4. directly copy the data file to the database data file (*. MDF) and log files (*. LDF) are copied to the target server and restored in SQL Server Query analyzer using the statement exec sp_attach_db @ dbname = 'test', @ filename1 = 'd: /MSSQL7/data/test_data.mdf ', @ filename2 = 'd:/MSSQL7/data/test_log.ldf'. In this way, the test database is appended to SQL Server and can be used as usual. If you do not want to use the original log file, run the following command: exec sp_detach_db @ dbname = 'test' exec sp_attach_single_file_db @ dbname = 'test', @ physname = 'd: the statement "/MSSQL7/data/test_data.mdf" only loads data files. log files can be automatically added by the SQL Server database, but the data recorded in the original log files is lost. 5. Application Program You can execute your own programs in the application (Pb, VB) or query analyzer. This method is flexible. In fact, you can use a platform to connect to the database, the main SQL statement used on the platform, which has little impact on the database. However, if a remote connection server is used, the transmission performance between networks is required to be good. Generally, there are two types of statements: 1> select... into new_tablename where... 2> insert (into) old_tablename select... from... where... the difference is that the former inserts data into a new table (create a table first and then insert data), and the latter inserts data into an existing table. I personally like the latter, because in terms of programming structure and application scope, the second statement is better than the former statement. 6. the replication function of SQL Server provides powerful data replication functions, which are also the most difficult to master. For specific applications, please refer to the relevant materials. It is worth noting that to successfully copy data, some conditions are required: 1> SQL Server Agent must be started, and MSDTC must be started. 2> all tables to be copied must have primary keys. 3> If the table has text or image data types, you must use the with log option. You cannot use 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. Operations that exceed this limit will fail. 4> on the computer to be copied, there should be at least implicit sharing, that is, the sharing name is C $ or d $ .... 5> the Windows NT account used by the SQL Server proxy cannot be a local system account because the local system account does not allow network access. 6> If the server involved in replication is in another computer domain, a trust relationship must be established between these domains.