Copy a database with data

This article describes how to create a database copy with a needed subset of data.

Sometimes you need to create a fresh database with copy of some data from your production or staging server. Debug some queries, create a report and so on. You can do it with SQL Data Examiner in couple mouse clicks.

Note: This article is based on SQL Data Examiner 2022 build 8.0.0.100. SQL Data Examiner 2018 and earlier versions cannot create a target database in a target database. However, you can still create new tables in a target database with SQL Data Examiner. Still, with SQL Data Examiner 2018, you need manually map all tables in a source database which you want to add to a target database with <New> table in the Add Custom Mapping... window.

Please follow the workflow below to copy data to a newly created database.

Create new comparison

Click the New... button on the toolbar. In the New Comparison window, specify a source server (a server you want to copy data), a database, and a target server (the server you want to copy the data to). Specify <New> as the target database to allow SQL Data Examiner to create a database for you.

Select tables you want to import

Since you selected <New> database on the previous step, SQL Data Examiner automatically maps all tables in the source database to <New Table> tables in the target database.

Select specific tables you want to copy to the target database to speed-up comparison.

If you need to copy only some data from a source table and do not need to copy all data, you can restrict the number of rows by applying a data filter. To do so:

1. Select a table in the table list
2. Click the Edit... button on the toolbar
3. Go to the Filter tab
4. Specify a WHERE clause without the WHERE keyword, for example LastUpdate > DATEADD(month, -1, GETDATE()) to copy all data for the previous month

Note: You can create new tables not only with data from tables but also from data from views or SQL queries.

Click Finish and SQL Data Examiner will load data from the source database.

Review and once again select data you want to copy

When data loading is completed, the program displays all loaded data to review data to copy.

Since you want to copy all loaded data, just select all tables in the list. For example, we selected all tables in the Person schema on the screenshot below.

Click the Synchronize... button on the toolbar to copy selected data.

Specify a new database name

Specify the name of the new database on the first step.

Click Next. You can usually remain default settings on the Configure Advanced Settings step.

Click Next. SQL Data Examiner displays the warning that tabes will be created in the target database.

Click Next. On the last step, click the Run synchronization.

The program starts data copying.

When the process is completed, you get the new database with selected data from the source database.