Five tips for SQL database migration)

I am a database administrator of a book company and want to maintain databases on multiple servers, I often move a database on a server to another server, and sometimes drag data from other branches to the company. So I have some experiences with data migration. I will share my experiences with you here, if you have any shortcomings, please kindly advise.

the DTs designer imports and exports data transmission. It has powerful functions and supports multiple tasks. It is also a visual interface and is 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 the tables.

in SQL Server Enterprise Manager, expand + on the left of the server, select database, right-click, 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.

and can be saved as another DTS package. If there are identical replication tasks in the future, run the DTS package directly, save time and effort.

You can also directly open the DTs designer by expanding data transformation services under the server name and selecting local packages, right-click the window on the right and select new package to open the DTs designer.

Note: If the table to be copied in the source database has a foreign key, pay attention to the moving sequence, the foreign key may be moved in batches. Otherwise, the index may be lost. The prompt next to the option is clear, or you can copy the key to the target database at a time, and then re-create the foreign key, primary Key, index.

back up the source database to a device using backup and recovery, copy the backup file to the target server (fast recovery) for database recovery. enter the name of the source database in the name of the recovered database (the name must be the same ), select forced recovery (which can overwrite the previous database options), and then recover from the device. You can select the backup file when browsing. This method completely restores the database, including foreign keys, primary keys, and indexes.

directly copy the data file to save the database data file (*. MDF) and log files (*. LDF) are copied to the target server and restored in SQL Server Query analyzer using statements: exec sp_attach_db @ dbname = 'test', @ filename1 = 'd: mssql7datatest_data.mdf ', @ filename2 = 'd: mssql7datatest_log.ldf 'so that the test database is appended to SQL Serve. R, which 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: mssql7datatest_data.mdf 'the statement 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.

In the application Program , you can customize programs that you can write in applications (PB and VB, it can also be executed in query analyzer. This method is flexible. In fact, a platform is used to connect to the database. The platform mainly uses SQL statements. This method 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 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.

SQL Server's replication function SQL Server provides powerful data replication functions, which are also the most difficult to master. For specific applications, see related materials, it is worth noting that to successfully copy data, some conditions are essential:

1) the 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 a text or image data type, 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. Operations that exceed this limit will fail.

4) on the computer to be copied, it should be at least implicit sharing, that is, the sharing names are A1, B1 ....

5) the Windows Server account used by the SQL Server proxy cannot be a local system account, because the local system account does not allow network access.