Creating Test Data from the Production Database With the SSMS Tools Pack

The SSMS Tools Pack created by Mladen Prajdić is a great extension for the SQL Server Management Studio. In this post I will explain how you can use it to create test data from a small subset of your production database.

 

From a bug in production…

Bugs happen, especially when complex data is involved. The most annoying bugs require a specific set of values to show up. Finding those involves most often a lot of debugging or a good logging infrastructure. When you find the offending combination, you could copy the whole production database and hand it to the developers. Data protection and the size of the database make this approach most often impossible, even if we ignore best practices and security. In this situation the SSMS Tools Pack can help you.

You start with a select that gives you the problematic rows from the first table in your production database. You can repeat the following steps with all other tables until you have all the data you need.
The extension created a new context menu entry in the result view called “Script Grid Results to SQL Editor“:

When clicked, a new SQL Editor window opens with a script like this one:

The script creates a temporary table, creates an Insert statement for every row in your result window, selects everything from the temporary table and then drops the temporary table. If you execute it, it will give you the same output as you had with your initial query.

Depending on the nature of the data, you can change the script to obfuscate or modify the values that are not needed to reproduce the bug.

 

… to the developer database

That script from above has some interesting capabilities. You can run in in any database and you will get the same result as in the production database. Therefore, you can give that to a developer who can run it to see the problematic data.

So far so good, but that script has one more trick to offer. All you need to do is to add an Insert statement right before the Select statement to persist this data:

If the script with the little modification is executed, the data is saved in the table of the developer database. From there it can be used with the application and further debugged to find out where exactly the problem occurs. The same works with the test database, where you can write an automated test that will check if the problem is fixed (and stays fixed).

 

Conclusion

Instead of creating a full copy of the production database you can use the SSMS Tools Pack to only get the few necessary rows out of the table and into the developer database. That is a lot faster and most likely more compliant with your data protection rules than sending your full production database to your developers.