Snap All the Things!

Database snapshots can be super handy for things like trying changes in a development environment, or for reverse engineering processes. For example, you can say, “Let’s run this test and then roll the database right back,” or, “Let’s run this process and see what it modifies by comparing the database to a prior snapshot.” That said, I always found it painful to compose the actual statements to create the snapshots, especially, say, for all the user databases on a whole instance. The pain is in the picky details of locating all the logical files and all the physical paths in all the databases. I thought, “Wow, it would be nice to just code-gen the statements to snap all the databases on an instance.”

Doing this code-gen in T-SQL is also tough, because you have to run queries against tables like sys.database_files, in the context of each database, and that leads to dynamic SQL and dynamic SQL leads inevitably to suffering. To the Shell!

Conceptually, then, we need to loop over all the databases on the instance, and for each database loop over all the data files. For each file, we need to construct a new physical file name, and then compose the resulting list of files into the middle section of the CREATE DATABASE statement.

In PowerShell, we can easily loop over all the databases and gather up all their data files with a combination of Get-ChildItem and foreach{}. Assuming you have the SQL Server 2012 sqlps module loaded, that’s something like this, replacing ‘localhost’ and ‘sql2012’ as appropriate for your machine:

In order to drill into the data files for each database, we need to go through the <database>\FileGroups node. We can do that with pair of nested loops, like the following. This example has some extra statements that just output the PowerShell paths, so we can see how we’re navigating the Database\FileGroups\Files tree:

At this point, we have the bones of that CREATE DATABASE statement – we just need to make one CREATE DATABASE statement for each database, and in the body of each of those statements compose an array of comma-separated ( name = x, filename = ‘y’ ) clauses. Here’s the resulting script, with one or two other features added: