T-SQL Tuesday 85: Managing database snapshots with dbatools

Dec 13, 07:41 AM


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, un/fortunately 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 [-Credential ][-Databases ] [-Exclude ]

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 [-Credential ] [-Snapshots ] [-Databases ] [-Exclude ]

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 communtity, you can find us on the SQL Community Slack feel free to send me a message if you need some help!
Constantine Kokkinos

,

---
---