The subsetter requires the target database to contain all the tables that will receive subset data, but without data rows in those tables. This page gives some hints on ways to create a new target database, or to delete all data from a target database. Some of the hints are SQL Server specific.

Contents

Create a target database with Flyway

If you have access to Flyway, you can create a target database using its baselining feature to create a copy of the source data without any data.

Creating a target database with DBCC CLONEDATABASE (Sql Server only)

The script below will create a schema-only copy of a database, containing all the tables but without data. It uses the T-SQL command DBCC CLONEDATABASE.

This script creates the target database in the same SQL Server instance as the source database. This can be useful when refining a subset configuration.

See the Microsoft documentation for details on the usage and limitations of DBCC CLONEDATABASE

Possible issue with DBCC CLONEDATABASE

There appears to be an issue with DBCC CLONEDATABASE which can sometimes prevent the clone from being created successfully. The symptoms are that an error message appears like the one below. If this occurs, our recommendation is to contact Microsoft technical support, or to try another method to create the target database such as exporting scripts.

Example error message: 

DBCC CLONEDATABASE (AdventureworksDW, AdventureworksDW_Copy) 
WITH VERIFY_CLONEDB, BACKUP_CLONEDB;  

Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'sys.sysschobjs' with unique index 'clst'. The duplicate key value is (341576255).

Steps

1. Edit the script replacing source_database_name with the name of your source database.
2. Similarly, replace target_database_name with the name you desire for your target database. You must replace both instances of the name.
3. Run the script, for instance in SQL Server Management Studio.

DBCC CLONEDATABASE(SourceDatabase, TargetDatabase) WITH VERIFY_CLONEDB
ALTER DATABASE [TargetDatabase] SET READ_WRITE WITH ROLLBACK IMMEDIATE

Creating a target database by exporting scripts (Sql Server only)

Sql Server Management Studio can be used to create SQL scripts that will create an empty copy of the source database. These scripts can be executed in a different Sql Server instance from the source database.

Step-by-step instructions are given in this Microsoft tutorial.