Subsetting SQL Server worked example
Published 16 October 2023
This section guides you through a worked subsetting example in SQL Server. It includes steps to create a small source database and to create an empty target database from it, which is then used in the subsetting example. It provides links to further examples and to resources to understand how the subsetter works.
Contents
Preparation
Preparation of Subsetter
Please do the following before beginning the worked example
- Install the subsetter CLI.
- Verify your installation by running the following command in a terminal window:
- Windows cmd:
subsetter.exe --version - Windows PowerShell:
./subsetter --version - Linux:
./subsetter --version
- Windows cmd:
The subsetter should report its version number as below (example in Windows cmd).
C:\Subsetting\>Subsetter.exe --version
0.8.0.0Preparation of Database Engine
Microsoft SQL Server comes with a command-line SQL query tool, sqlcmd. It can be found under the directory where you have installed Microsoft SQL Server, in Windows this is typically C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn. You can run sqlcmd by opening a command prompt directly in this directory, or add the directory to your PATH environment variable to enable running sqlcmd from any where on your computer.
Enter sqlcmd in a command prompt and you should see the following:
1>
Now you have entered sqlcmd and you should be able to execute SQL queries against the databases you created. To exit sqlcmd, simply type:
quit
Create a source database
Create an empty database
Create an empty database called SourceDatabase in SQL Server.
You can do this using a graphical tool like SQL Server Management Studio (SSMS).
Alternatively you can do this by executing this SQL query below, either in SSMS or in sqlcmd.
CREATE DATABASE [SourceDatabase]; go
Populate the database
Download the SQL script sample-sql-server-database.sql and execute it in the new database.
Again, you can do this using SSMS, or you can execute the command below at a command prompt (NOT inside sqlcmd):
This will create a sample database with four tables, containing example data. It represents a simple social media website, where users create posts and comment on posts, and users belong to organizations.
Check the database contents
Check that the tables exist and contain data. Use SSMS, or use a SQL query as below which lists all the table names:
SELECT TABLE_NAME FROM SourceDatabase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'; go
And a SQL query as below which to list the data in a table:
SELECT * FROM SourceDatabase.dbo.Users; go
Create an empty target database
Run the SQL script given below. This will create a new database called TargetDatabase. It will have the same tables as the SourceDatabase, but without any data rows.
DBCC CLONEDATABASE(SourceDatabase, TargetDatabase) WITH VERIFY_CLONEDB; go ALTER DATABASE [TargetDatabase] SET READ_WRITE WITH ROLLBACK IMMEDIATE; go
Verify that the target database exits with the same schema as the source database and is empty.
Run the subsetter
The commands below run the subsetter. If the commands cannot run successfully, you may need to work through the connection strings section below to configure them to your environment.
Click on the sections below to view the example command lines:
Connection strings
You may need to change the connection strings to suit your environment. The connection strings identify the source and target database.
The connection strings in the example above use Windows security. An alternative is to set trusted_connection=false , and use uid=username and pwd=userpassword to specify a username and password with SQL Server's integrated security.
We have further advice on connection strings in our troubleshooting and known limitations section. There is also a lot of information online about connection strings. Here are some tips:
- Use
serverto specify the SQL Server instance containing the databases. In the example above we are usinglocalhostto connect to an instance running on the same computer that is running the subsetter. - Use appropriate attributes to connect to the instance in a secure way. The example uses
TrustServerCertificateto connect to a locally-hosted instance of SQL Server which uses a self-signed certificate. This may not be appropriate in your case. You may wish to ask your IT department for advice.
Output from the command
The command will produce detailed output similar to that shown below. The details may vary. Check that the last line of output says Subsetting completed.
Check the data in the target database
Check the contents of the tables in the target database. You should find that each table contains some data rows, but fewer than in the source database.
Congratulations! This shows that the subset has been created successfully.
Further examples
The documentation on configuration files explains the formats and provides worked examples of their use, that build on this worked example.
If you want to experiment with different filter clauses, please checkout subsetting example filter clauses. You might find it helpful to apply --target-database-write-mode Overwrite so that you can reuse the target database which has now been written to.
Learning more about how the subsetter works
Now that you have carried out the worked example, you might like to understand more about how the subsetter works. Please consult the page Using the worked example to understand the subsetter. This page uses the output and the commands from the worked example to explain how the subsetter produces a referentially intact subset.
