PowerShell, Scripting, SQL
comments 6

Creating SQL snapshots using SMO and PowerShell

First of all, I am not SQL guy. I work on SharePoint quite a bit and venturing into SQL now. So, the first thing is to learn how I can script my SQL management tasks. I know there are quite a few things on the Internet but exploring all this myself gives an opportunity to learn how SQL works.

This post is about how we can use SQL Management Objects and PowerShell to create database snapshots. Make a note, I did not mention T-SQL. I am not sure if someone else already a wrote post around this or not. But, this is just the way I learned to do it. Do let me know if you know a better way or done it earlier.

With no further delay, here is the script.

I have enough inline comments in the above code to explain what it is doing. Remember, you need SQL Enterprise edition to use DB snapshots.

Snapshot

Database Snapshot

This is it. I am off to exploring how I can revert a snapshot without using T-SQL and using pure SMO and PowerShell. I shall put together all the snapshot related functions as a PowerShell module after I complete my research.

Don’t forget to leave a comment here , if you have any comments or feedback.

Filed under: PowerShell, Scripting, SQL

by

Ravikanth is a principal engineer and the lead architect for Microsoft and VMware virtualized and hybrid cloud solutions within the Infrastructure Solutions Group at Dell EMC. He is a multi-year recipient of Microsoft Most Valuable Professional (MVP) award in Windows PowerShell (CDM) and Microsoft Azure. Ravikanth is the author of Windows PowerShell Desired State Configuration Revealed (Apress) and leads Bangalore PowerShell and Bangalore IT Pro user groups. He can be seen speaking regularly at local user group events and conferences in India and abroad.