SQLServer -- How to completely copy a database, sqlserver Database

SQLServer -- How to completely copy a database, sqlserver Database

For the sake of conciseness, the source database is referred to as S, and the target database is called T.

SqlDelta is required in this article. Click to open the link.

1. Right-click the source S to generate a database creation script;

2. Modify the script to create the target database T, mainly to change the original database name and file path to the actual one.

3. sqlserver2014 allows you to directly view partition functions and partition schemes, right-click to generate related creation statements, and then execute them on T.

4. Open the SqlDelta interface. The Source is the old DB, and the Target is the new DB. Note that the source connection permission should be at least dbowner; otherwise, scripts such as stored procedures cannot be generated.

If the configuration is complete, click Run in the upper-right corner.

5. For example, remove unnecessary items (such as users) and click Generate script in the upper right corner.

6. Copy the generated scripts to the "new query" Window of the new DB. The execution is half done.

7. After the execution is complete, click Refresh in to compare the table and view information again to see if there are different items.

If there is a difference, copy the corresponding script and re-execute it until the two are completely consistent.

For example, the Red Circle is different from the source and target.