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:
[sourcecode language=”SQL”]
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
[/sourcecode]
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:
[sourcecode language=”SQL”]
Select * from [sys].[sysdatabases]
[/sourcecode]
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]”:
[sourcecode language=”SQL”]
SELECT [name]
,[database_id]
,[source_database_id]
,[owner_sid]
,[create_date]
FROM [master].[sys].[databases]
[/sourcecode]
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.
[sourcecode language=”PowerShell”]
Import-Module SQLPS -DisableNameChecking
$MySQL = New-Object Microsoft.SqlServer.management.Smo.Server ‘YourServername\InstanceName’
$MySQL.Databases | Select name
[/sourcecode]
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.
[sourcecode language=”PowerShell”]
($MySQL.Databases) | Get-Member | Out-Gridview
[/sourcecode]
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:
[sourcecode language=”PowerShell”]
$MySQL.Databases | `
Select name, Owner, RecoveryModel, `
IsDatabaseSnapshot, DatabaseSnapshotBaseName `
FT -AutoSize;
[/sourcecode]
Conclusion
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!