Let’s get the disclaimer out of the way right up front. This article is not intended to be used in place of a Production grade backup solution. It can be used in conjunction, with your regular backup rotation, but if you truly care about the database and its contents, please, refer to books on-line or read this Introduction to Backup and Restore Strategies in SQL Server.
If you already know all that and want a quick and dirty way to test database modifications or statements in a development environment…proceed.
While not a developer by trade…there are circumstances which call for me to wear a different hat. When that happens…I typically have to develop, and document, an entire change process. No different than “a real developer”. The steps are then submitted to a change review board, approved, scheduled, and applied to our production environment.
Going in, I have an idea how it will go. As the process unfolds, undoubtedly something will occur, or reveal itself, that I hadn’t thought of and the documentation changes a bit…with each iteration.
Back in the day…when we had to walk both ways, uphill, in the snow, to school…we had to do a backup to disk, run through the changes, document the process, restore the database…and then do it all over again. The backup and restore process, with a database of any size, can take a fair amount of time.
Enter “SQL Snapshot Backups” – While the dress rehearsals, today, haven’t changed much…the backup and restore process has. Instead of it taking minutes or hours…taking a snapshot backup or reverting to a previous one takes seconds. Since each run-thru is that much quicker…you can focus on getting the process and documentation right.
The syntax below can be used to create a Snapshot of “Adventureworks”. A couple catches…the NAME is the logical name, of the data file, within the database and the path to the FILENAME, you specify, must exist.
–Create a database snapshotCREATE DATABASE Adventureworks_snapshot ON ( NAME = AdventureWorks_Data , FILENAME = ‘D:\Backup\Adventureworks_Snapshot’ ) AS SNAPSHOT OFAdventureworks
The Snapshot should then be visible in the Database Snapshots folder:
Once you have confirmed the Snapshot has been taken you can run through your modifications, document, and verify your process. Should you need to, use the syntax below to revert back to the state the database was in when the snapshot was initially taken…and run through it again.
— Reverting AdventureWorks to AdventureWorks_snapshotUSE master; RESTORE DATABASEAdventureworks from DATABASE_SNAPSHOT = ‘Adventureworks_snapshot’; GO
When you are comfortable with your results and have the change process fully documented you can use the following to delete the snapshot:
–To Delete AdventureWorks Snapshot DROP DATABASE Adventureworks_snapshot