THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

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!

The statements we need to compose look like:

CREATE DATABASE [myDB_Snap] ON
    ( name = LogicalName1, filename = 'PhysicalPath1' ),
    ( name = LogicalName2, filename = 'PhysicalPath2' ),
    <etc.>
    ( name = LogicalNameX, filename = 'PhysicalPathX' )
AS SNAPSHOT OF [myDB] ;

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:

Set-Location SQLSERVER:\sql\localhost\sql2012\databases
Get-ChildItem | 
foreach {
    # Looping Through DBs
    $_.name
}

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:

Set-Location SQLSERVER:\sql\localhost\sql2012\databases
Get-ChildItem | 
foreach {
   "Database: $($_.name)"
   $fgroot = ( join-path $_.name 'FileGroups' )
   "Location of FileGroups: $fgroot"
   Get-ChildItem $fgroot |
   foreach {
      $fgpath = ( join-path $fgroot $_.name )
      "Location of Files: $fgpath"
      Get-ChildItem ( join-path $fgpath 'Files' ) |  
      foreach {
        "File: $($_.name) at $($_.filename)"
      }      
   }
}

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:

# Compose a T-SQL Script to snap all the user databases on an instance

# Databases to skip:
$excludedDBs =  @( 'master','model','msdb','tempdb' )

# Suffixes to add to the snapshot files and the db snapshot name:
$fnsuffix = 'ss'
$DBsuffix = '_SNAP'

# Arrays/variables used to build the SQL statements:
$sqlstmtarray = @()
$onclausearray = @()
$sqlstmt = ''

Set-Location SQLSERVER:\sql\localhost\sql2012\databases

Get-ChildItem | 
where { ! ( $excludedDBs -contains $_.name ) } |
foreach {
   # Loop over databases on the instance

   $fgroot = ( join-path $_.name 'FileGroups' )
   Get-ChildItem $fgroot |
   foreach {
      # Loop over the filegroups in the current DB

      $fgpath = ( join-path $fgroot $_.name )
      Get-ChildItem ( join-path $fgpath 'Files' ) |  
      foreach {
        # Loop over the data files in the current filegroup

        # Compose a line like '( name = logicalname, filename = physicalname + suffix )' for each file
        # and add the line to an array for use later
        $onclausearray += "`( name = [$($_.name)], filename = `'$($_.FileName)$($fnsuffix)`' `)"

      }      
   }
   
   # Use the results to compose a Create Database statement 

   $sqlstmt = "CREATE DATABASE [$($_.name)$($DBsuffix)] ON`n"
   $sqlstmt += ( $onclausearray -join ", `n" ) # –join converts the array to a comma-sep list
   $sqlstmt += "`n"
   $sqlstmt += "AS SNAPSHOT OF $($_.name) ;"
   $sqlstmtarray += $sqlstmt
   
   # Clear variables for the next iteration
   $sqlstmt = ''
   $onclausearray = @()
}

# Output all the Create Database statements

$sqlstmtarray

That’s that! This works for most simple cases, by putting the snap files in the same directories with the original files, and just adding ‘ss’ to the end of the source databases’ physical file names.

Published Thursday, February 20, 2014 3:00 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Wayne Sheffield said:

Nice handy script Merrill. Thanks for sharing!

February 21, 2014 9:04 AM
 

Ola Ekdahl said:

Oh my! This is great!

February 21, 2014 10:35 AM
 

ALZDBA said:

Hi Merrill,

Nice Script.

However, since more and more DBA are no longer Windows Admins on the SQL boxes ( :-(  ) , how about using only SQLPs to fulfill the quest ?

# Loop over databases on the instance

   $_.FileGroups | % { $_.Files | select Name, FileName } | % {

       # Loop over the data files in the current filegroup

       # Compose a line like '( name = logicalname, filename = physicalname + suffix )' for each file

       # and add the line to an array for use later

       $onclausearray += "`( name = [$($_.Name)], filename = `'$($_.FileName)$($fnsuffix)`' `)"

       }

September 4, 2014 6:09 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement