T-SQL & PowerShell – Another way to Identify Database Snapshots

Just a quick blog on spotting your Database Snaphots. I just couldn’t believe that I’ve been missing creating SQL database snapshots but sometimes having so much work make you blind.  I’ve been using a lot Hyper-V Snapshot features and recently (Thanks to Chad Miller) I got the chance to create and test a few.

So, first we need to create a new db snapshot of my AdventureWorks database using T-SQL Script:

CREATE DATABASE AdventureWorks_dbSnapShot_0001 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSQLDENALICTP3\MSSQL\DATA\AdventureWorks_Data_0001.ss' )
AS SNAPSHOT OF AdventureWorks;

Now, I go to SSMS and verify my new database snapshot exist by going into the ‘Object Explorer’ and looking under ‘Database Shashots’ folder.

Using T-SQL

If I use the following T-SQL command to list all my databases:

Select * from [sys].[sysdatabases]

I will get all of them listed including the snapshots. So, here’s another way to use T-SQL to identify all database snapshots. For that, I’m going to do a select and use from the Master db the view named “[sys].[databases]”:

SELECT [name]
  FROM [master].[sys].[databases]

In this result, pay attention to the “[source_database_id]” column. Notice that this column is mostly “null” except when if it has a value. This value will match the “[Database_ID]” column. So, you can use this to identify database snapshots.

Using PowerShell

Now, let’s take a look on how to use PowerShell to identify database snapshots using SMO. In the following code I’m listing all databases but snapshots are also included.

Import-Module SQLPS -DisableNameChecking
$MySQL = New-Object Microsoft.SqlServer.management.Smo.Server 'YourServername\InstanceName'
$MySQL.Databases | Select name

So, in order to identify the database snapshot I need to look deeper into our .NET database object. Using the “Get-Member” command I can see what’s object are available.

($MySQL.Databases) | Get-Member | Out-Gridview

Using the “Out-Gridview” command to view my results a separate popup window, I found two properties of interest:

  1. IsDatabaseSnapshot‘ – which show “true” us if is a snapshot.
  2. DatabaseSnapshotBaseName‘ – which give us the origne of database name of the snapshot.

So, now I can use the following PowerShell commands to show all my databases and identify the snapshots:

$MySQL.Databases | `
   Select name, Owner, RecoveryModel, `
   IsDatabaseSnapshot, DatabaseSnapshotBaseName `
   FT -AutoSize;


Using both T-SQL and PowerShell examples, now you have a startup point to spot and take some control over your database snapshots.

Happy PowerShelling!