I am modifying a number of SSIS packages that extract, transform, and load data into various tables. While unit testing I need a quick way to refresh my development environment. The traditional method is to take a backup and restore but if the database is large it can take a significant amount of time – even a ten minute restore drags on forever if you have to run it a dozen times a day. Another option is to duplicate the data in separate tables then kill and fill to refresh the data. This can run quickly (depending of course on table sizes, indexes, etc.) but it can be complex to set up. My favorite approach is to create a database snapshot as my backup, run the load, evaluate the results, then restore from the snapshot – quick and easy to set up. (If you are not familiar with database snapshots here is a good overview.)
Quick
Due to their sparse file architecture DB Snapshots are created instantaneously, all that gets created is an empty file. As changes are made to the original the altered 8KB pages are written to the new file with no logging – in fact the snapshot doesn’t even have a transaction log. When you want to restore from the snapshot the copied pages are just copied back to their original location. If your changes are relatively small in terms of 8KB pages compared to the total DB you will find this lightning fast.
Easy
A DB Snapshot is as easy to create as a regular DB. The only complexity is if your DB has multiple files so must your snapshot. Never fear though, I have a code generator that will handle all the gory details, see below. Please be sure you read the comments at the top so you know how to run it. There is also code at the end of the script to restore to the original state as well as to drop the snapshot.
Caveats
Restrictions
- Snapshots are only available in Enterprise Edition.
- The Snapshot is Read-Only which shouldn’t be a problem when used only for restores.
- Creating a Snapshot requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
Size Issues
Normally DB Snapshots don’t take up much space when used this way but be sure to clean up after yourself! Since the snapshot keeps collecting changes if you forget to drop the Snapshot it will continue to grow to the size of the original if enough changes are made.
Exclusive Use
You are not required to have exclusive use except for the restore but if your buddies are using the original DB and you do they restore the may not be your buddies anymore. Remember, with great power comes great responsibility.
Code
I apologize for the code formatting issues, WordPress and I are having issues. 😦
| /**************************************************************** |
| This script will write a script to create a snapshot of the source DB, it will |
| handle multiple files in the DB. It also supports multiple snapshots of the |
| same source by changing @SSName. |
| Set @SourceDBName to the name of the DB you want a snapshot of. |
| Set @SSName to what you want to call your snapshot. |
| Ctrl+Shift+M will allow you to enter the DB and Snapshot names. |
| You can enable the Execute statement at the end if you want to build the |
| snapshot now. |
| ********************************************************/ |
| Declare |
| @SourceDBName As sysname |
| ,@SSName As sysname |
| ,@SQL as Varchar(4000) |
| Select |
| @SourceDBName = ‘<SourceDBName,sysname,Essence>’ |
| ,@SSName = ‘<SSName,sysname,EssenceSS>’ |
| SELECT |
| @SQL = |
| ‘CREATE DATABASE ‘ + @SSName + ‘ ON ‘ |
| + LEFT(Script,LEN(Script)-1) |
| + ‘AS SNAPSHOT OF ‘ + @SourceDBName |
| FROM |
| (SELECT |
| ( |
| SELECT col + ‘, ‘ as [text()] |
| FROM |
| ( |
| Select Top 100 |
| Case |
| When Right(mf.physical_name,4) = ‘.ndf’ |
| Then ‘(NAME = ‘ + mf.name + ‘ ,FILENAME = ”’ |
| + Replace(mf.physical_name,’.ndf’,’_’ + @SSName + ‘.ss’) + ”’)’ |
| Else ‘(NAME = ‘ + mf.name + ‘ ,FILENAME = ”’ |
| + Replace(mf.physical_name,’.mdf’,’_’ + @SSName + ‘.ss’) + ”’)’ |
| End As col |
| From |
| sys.master_files As mf |
| Inner Join |
| sys.databases As db |
| on mf.database_id = db.database_id |
| Where |
| db.name = @SourceDBName |
| And |
| mf.type_desc = ‘ROWS’ |
| Order By |
| [file_id] |
| ) As A |
| ORDER BY col ASC |
| FOR XML PATH(”) |
| ) AS Script |
| ) B |
| –Execute (@SQL) |
| Print @SQL |
| ——————————————————————————————– |
| –Other useful code |
| /*Restore from snapshot********************************** |
| Use Master |
| Go |
| Alter Database <SourceDBName,sysname,Essence> |
| Set RESTRICTED_USER With ROLLBACK IMMEDIATE |
| Go |
| Restore Database <SourceDBName,sysname,Essence> |
| From Database_Snapshot = ‘<SSName,sysname,EssenceSS>’ |
| Go |
| ******************************************************/ |
| /*Drop Snapshot***************************************** |
| Drop Database <SSName,sysname,EssenceSS> |
| ******************************************************/ |
Philergia 
This is great. One thing I had to change is adding brackets around the sysnames in the dynamic SQL because some of my names have a dash/space in them. Other than that, I want to thank you for saving me lots of time!
Thanks for the feedback. I know what you mean about the spaces – I HATE spaces in names. 🙂