Can’t stress enough how handy database snapshots (compatible only in Enterprise and Developer editions of SQL Server 2005+) come in when testing bulk imports, data scrubbing, or any sort of data / schema modification script. In nearly no time, I can create a snapshot (via shortcut snippet), run my script – don’t like the results? My script crapped out the data? I can run an instant undo by recovering from the snapshot, which works pretty fast. If I want to create another snapshot, I usually tend to overwrite the snapshot I created, and then create it again.
It’s so handy that I have a snippet for it:
Anywhoot, here’s how you create a snapshot:
-- CREATING A SNAPSHOT CREATE DATABASE YourDatabase_snap ON -- Name of new snapshot ( NAME = yourdb_data, -- Logical filename of source db you want to snapshot FILENAME = 'c:\YourDatabase_data_1800.ss' -- Sparse file to create ) AS SNAPSHOT OF YourDatabase; -- Source db name
-- RECOVERING FROM A SNAPSHOT USE master; RESTORE DATABASE YourDatabase -- Source db name where the data resided FROM DATABASE_SNAPSHOT = 'YourDatabase_snap'; -- Snapshot db name
Deleting a snapshot is just like dropping a database.
DROP DATABASE YourDatabase_Snap