Automating your snapshots

A little while ago I was talking to someone about possibilities of reporting from SQL Server to avoid impacting the production server and the very first thing that sprang to mind was for them to use snapshotting from a mirror. They had already considered this option but it was not really viable because an almost real-time reporting solution was required. It wasn’t until later that I wondered whether I had not really been precise enough in my explanation.

Since creating a snapshot can be pretty much instantaneous due to the way in which their mechanics work, as long as “real-time” means the time at any one instant, then there is no reason why snapshotting cannot be the solution for you. The only problem then that remains is how you are going to manage your snapshots, namely creation and destruction. Since the initial overhead of setting up (and then removing) a snapshot is relatively low, it makes perfect sense in my opinion to generate snapshots for reporting and then tear them down post report creation.

When you are creating a snapshot, since you must specify all datafile locations, auto-generation of them may at first seem a little more complicated than it really is. In truth, it is not particularly difficult and I have written some fairly useful code which can create a snapshot for you easily. Obviously you can customise the naming conventions of the snapshotted database to your requirements. Your reporting solution then could call a procedure to create the report snapshot (using the code below) on your mirrored database. All that would remain is to tear the snapshot down once the report has completed. I shall leave it to your imagination how you want to tear down your snapshots since depending upon your naming convention your method might differ slightly, but querying snapshots in existence is very simple as follows :-

SELECT,f.physical_name FROM sys.databases d JOIN
sys.master_files f ON d.database_id=f.database_id
WHERE d.source_database_id=

The procedure does however have an output parameter that returns the created snapshot name and you can use it in your reporting TSQL code to tear the snapshot down post report generation. Its up to you, but it is there if you want it!

We then move on to the main code itself to auto-generate your database mirror snapshot. The first thing you need to do is to decide which database you are going to deploy this stored procedure to. I personally prefer to have a dedicated database administration database that has all the custom written procedures, functions and the like housed in there. Once you have changed context into the database of choice your then need to execute the usp_createsnapshot creation script which can be found here to deploy.

Once this procedure is deployed, all you need to do from now on in order to create a quick snapshot on any of your databases or mirrors located on this server is simply call the proc and pass in the name of the database or mirrored database and execute and hey presto a database snapshot has been created for you 🙂
Below I have entered a few examples of how the procedure works and it’s error handling and feedback. The final example is valid input.

EXEC dbo.usp_createsnapshot @help=1
EXEC dbo.usp_createsnapshot master
EXEC dbo.usp_createsnapshot invaliddbname
DECLARE @ss sysname
EXEC dbo.usp_createsnapshot msdb,@ss OUT
PRINT 'Snapshot ' + @ss + ' created.'

Within this following screenshot are the results, and in particular the output within the red box demonstrates the correct use of the stored procedure.

Well that’s the end of this post ladies and gentlemen. I hope you find this stored procedure as useful as I do, and remember ….Happy Reporting!

5 thoughts on “Automating your snapshots

  1. Bill Graziano

    Interesting article. We do something similar but have a single snapshot that resets every hour. So you have multiple overlapping snapshots?

    We’ve also found that creating a snapshot can sometimes take longer if there many transactions in flight.

  2. retracement Post author

    Welcome! Its great to have people in the industry who I respect immensely dropping by, most recently Joe Webb and now yourself -very grateful 🙂

    That’s a great point you raise, because I think in my post I don’t necessarily make this clear. I think the whole point of the procedure is to provide the ability to create and destruct a snapshot easily without having to worry about the plumbing (files and syntax) -something along the lines of “create me a snapshot and tell me what you called it”, obviously knowing the name allows for easy tear down.

    So I’m aiming it really towards temporary adhoc snapshot use, where the snapshot will be removed once its reason for existence ends. This is one of the reasons for using the unique naming on the snapshot to avoid conflicts should any other snapshots already exist. Therefore yes, there could be situations with this procedure where the multiple overlapping snapshots will exist and I guess this would be something that ought to be closely watched. For instance if a reporting solution is implemented as I suggest, and lots of reports were ran fairly closely to each other I would worry about the impact on the server should it be having a high volume of DML changes. I believe Paul Randal has suggested in the past to avoid running more than two snapshots at a time on any one database, so I personally try to avoid running more than one at a time where possible except of course when creating a temporary one makes sense.

    Probably the most useful reason to use my procedure is in situations where you are rolling out a promotion and as a last line of defence to improve recovery time create a temporary snapshot until sign off has been received.

    I’ll certainly look into the suggestion about the # of transactions effecting snapshot creation time. I am sure you are absolutely right and I would love to see how bad things could get.

    Thanks again for your comment.

  3. jon

    Darn, not supported on Standard version of SQL server. I was hoping it would work for me. Does the DEV version of SQL support snapshots?

    1. retracement Post author

      Yes, Developer Edition is essentially a re-badged “Enterprise” Edition. Go to this url and you will see the line that states “SQL Server 2008 Developer includes all of the functionality of Enterprise Edition”.

      Its a shame of course that snapshots are a ENT(/DEV) only but I guess it is one more reason to buy Enterprise if there are also other justifications for you to do so.

      Thanks for dropping by.

  4. Wilfred van Dijk

    Looks very similar to a stored procedure I posted some time ago on SQLServerCentral. The thing you’re missing in this procedure is to check if the datafiles are ONLINE (or you’ll get an error)

    select	name
    from	master.sys.master_files
    where	database_id = DB_ID(@Databasename)
    and		type_desc in ('ROWS','FULLTEXT')
    and		state_desc = 'ONLINE'

Comments are closed.