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; GO
Now, I go to SSMS and verify my new database snapshot exist by going into the ‘Object Explorer’ and looking under ‘Database Shashots’ folder.
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] ,[database_id] ,[source_database_id] ,[owner_sid] ,[create_date] 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.
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:
- ‘IsDatabaseSnapshot‘ – which show “true” us if is a snapshot.
- ‘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.