Press "Enter" to skip to content

Month: December 2016

T-SQL Tuesday 85: Managing database snapshots with dbatools

ck ck
December 13, 2016
T-SQL Tuesday 85: Managing database snapshots with dbatools

For my second TSQL Tuesday post, I wanted to introduce a nifty feature in the dbatools lineup called Restore-SQLBackupFromDirectory, which used Ola Hallengren’s scripts as a baseline to restore a server from a backup folder, however during writing this I came across a newer command that Stuart Moore engineered (Restore-DBABackup) which made it entirely redundant, expect his post coming very soon.

Since there was a bit of a last minute pivot, we are going to do a whirlwind tour of two new PowerShell commands that just hit the lineup regarding database backups, Get and Remove-DbaDatabaseSnapshot.

These along with an additional function coming soon (New-DbaDatabaseSnapshot) are going to make it super simple to create, list, and remove database snapshots on your SQL Server instance.

Command Reference

Listing snapshots on a target SQL Server

Get-DbaDatabaseSnapshot -SqlServer <string[]> [-Credential ][-Databases <string[]>] [-Exclude <string[]>]

Returns a list of database snapshots on your SQL Server.

Accepts multiple SQL Servers, and can filter inclusively or exclusively on the base database name to find snapshots.

Example

Removing snapshots on a target SQL Server

Remove-DbaDatabaseSnapshot -SqlServer <string[]> [-Credential ] [-Snapshots <string[]>] [-Databases <string[]>] [-Exclude <string[]>]

Drops snapshots from your SQL Server.

This has an additional unique parameter of -Snapshots, which allows you to filter on exact snapshot name instead of using a base database name (in the case of multiple snapshots you want to remove.)

Example

Common Parameter Reference

As with many commands in the dbatools lineup there are several common parameters:

  • SqlServer – Indicates what server you would like to operate on.
  • Credential – Allows you to include an overriding SQL Login credential (PowerShell object),
  • Databases – Operates as an inclusion filter for database names.
  • Exclude – Operates as an exclusion filter for database names.

Final Notes:

Hopefully after reading today you have two commands ready to help you manage your database snapshots, and I have whet your appetite for the upcoming restore and snapshot commands coming soon!

  • If you don’t see the filtering/parameter populating from tab completion, this indicates an issue connecting to your SQL Server.
  • Both of these commands were written recently by one of the newest contributors to the project, niphlod great job!
  • If you want to learn more about dbatools (free PowerShell module with nearly 100 SQL Server administration, best practice and migration commands included) checkout https://dbatools.io we have a growing team and we want contributions from busy SQL DBAs like yourself.
  • If you are unsure where to start or just want to join the growing community, you can find us on the SQL Community Slack feel free to send me a message if you need some help!