Database Snapshots in SQL Server

Ever wanted to make a read-only point-in-time copy of a database, and wondered which technique to use? Microsoft SQL Server 2005 provides a plethora of ways to do this, including database backup/restore, database detach/re-attach, log-shipping, replication, mirroring, and so forth. However, one method available in the Enterprise edition, the Database Snapshot, is new to SQL Server 2005, and is worth taking a closer look.

Why are Database Snapshots Useful?
There are many applications where a point-in-time snapshot is useful. Microsoft suggest the following use cases:

  • Reporting up to a specific time period, ignoring later data
  • Reporting against mirror or standby databases that are otherwise unavailable
  • Insuring against user or administrator error, providing a quick way to revert to an older version of the database
  • Managing test databases, particularly during rapid feature and schema development

Of course, these needs could be served by a database backup or attached copy of a database, but the key benefit of choosing a snapshot over one of the other methods is simple: creating a database snapshot is fast.

Creating and Using Database Snapshots
Creating database snapshots is easy – it’s a CREATE DATABASE statement, specifying only the logical and physical filenames. Remember it’s a read-only snapshot, so we don’t need to add autogrowth or transaction log settings. Here’s the code:

CREATE DATABASE AdventureWorks_Snapshot_Monday ON 



Snapshot creation is not supported by the Object Explorer interface in Management Studio; you must use a CREATE DATABASE statement as above, with the AS SNAPSHOT OF clause indicating the source database. Also, note that only the Enterprise edition of SQL Server 2005 supports database snapshots.

The snapshot contains a version of the data as it existed at its creation, having rolled back uncommitted transactions. This means that otherwise unavailable databases, such as mirrors and standby servers, can be used to create snapshots.

Having created a snapshot, you can now use it as you would any other read-only database; all objects are exposed in exactly the same way, via Object Explorer, scripts, or reporting tools.

Reverting a database to the version stored in the snapshot is similarly easy:



This returns the database to the state it was in when the snapshot was created, minus any uncommitted transactions – remember that a snapshot is transactionally consistent at its creation. Note that restoring from a snapshot renders all other snapshots unusable – they should be deleted and re-created if required.

How do Database Snapshots work?
A Database Snapshot looks like an ordinary read-only database, from the user’s point of view; it can be accessed with a USE statement, and can be browsed from within Management Studio. However, it initially occupies almost no disk space, and so can be created almost instantly. This magic is achieved via an NTFS feature, sparse files. A sparse file is a file that may appear to be large, but in fact only occupies a portion of the physical space allocated to it.

Now, because a database snapshot presents a read-only view of your source database, it need not store a copy of every page. Instead, SQL Server performs a copy-on-write operation; in the source database, the first time a data page changes after the creation of a snapshot, a copy of the original page is placed in the sparse file. The snapshot serves data from the snapshot copies where source data has changed, and the original source pages when they are unchanged.

Best Practices
Sometimes you will choose a copy of a backup over a snapshot, sometimes it’ll be a detached copy of the data file. However, for many situations your best bet is a database snapshot, so it’s worth keeping some points in mind. In particular:

  • The file size will look considerably larger than the space it consumes on disk, and should be clearly marked as a snapshot for this reason. Use explicit naming conventions to make it clear to administrators.
  • Snapshots are at their best when young and fresh, and don’t take up too much space. If you need to keep a snapshot for any length of time, consider using another method to create your read-only copies.
  • As snapshots persist until deleted, you will need to explicitly rotate snapshots, either manually or with a script.
  • Performing index operations such as defragmentation or index rebuilding will modify so many pages that the snapshot will likely contain a complete copy of the source data for that index. The more snapshots there are, the more copies will exist.
  • If the disk containing a snapshot fills up, and a page write fails, the snapshot will become useless, as it will not contain all necessary pages. Make sure the disk can’t fill up!

Database snapshots are a worthwhile addition to the arsenal of any SQL Server DBA, and fit well with other techniques, particularly when you may need to quickly revert a database, or if you need to maintain rolling snapshots. Remember the key advantages: high speed and low physical size. But also remember that these advantages diminish as the snapshot ages and grows, and if the number of snapshots increases.

Above all, database snapshots are fast and easy to use; it won’t cost you anything to try them out, and you will probably find them very useful indeed. If all you need to do with a point-in-time copy is select from it, or possibly revert to it, then a database snapshot is likely the best choice available.

Snapshot Image Cleaning

Time is precious and we don’t want it to slip away that easy without taking advantage of its significance. To circumvent waste of time, Snapshot can be a great help to us.

Snapshot is a valuable technique to save our precious time when working with images in Photoshop – be it an original copy image, something from a digital camera or from a scanned slide or negative. This is usually used to clean up images such as images for publication use.

The technique is so easy, it only involves a four-step method and is easy to recall. You can make use of this in many situations like adjusting levels, color, saturation, sharpness and other corrections without wasting time with layer masks, lasso tools, layers, rubber stamping or magic wand. To ensure that your eyes are fixed to your subject, you can use this method.

Soft-eyes are scanning problems inherent of the natural film curvature while scanning. To avoid soft-eyes problems, use snapshots technique which allows fixation in few seconds. To do this, first open your file. Then go to filter > sharpen > unsharp mask. After that, a dialog box will appear with unsharp mask defaults. Set the default amount to 75 percent, radius to 1 pixel and threshold levels to zero or one. Finally, click OK. You may notice that the entire image has the unsharp mask applied to it beforehand. As a result, it sharpens the overall image.

The texture is no longer as soft as it first appeared. This is the time when the snapshots will save your artwork.

To correct the flaws, you must go to the history palette. If it you did not find it in your desktop, you can find it normally on the right in Photoshop by default. Go to window > Show history. Once you have located your history palette, select the bold arrow-button on its right side. Click and hold it until a menu appears then go to New Snapshot. When you release your mouse, another dialog box will appear. You must enter in it the name “unsharp eyes”, then click OK.

If you have more than one snapshot in a working file, do not forget to label your snapshots to be able to have a better workflow management when it comes to organization of your images. It is nice to do it that way for easy and fast selection and for an efficient scheme.

Progressive Snapshot Discount

When you get a quote on car insurance, you may be asked for details such as your real name, date of birth, the zip code you live in and your favorite ice cream flavor. OK, maybe not the last one, but the others are pretty much the standard questions, regardless of the insurance company. I often get asked “Why do you need my real name” or “what does my zip code have to do with auto insurance?”. While there is a lot of details needed to properly answer these questions, the simple explanation is that insurance companies use statistics as a way to determine your rate. So, if you live in a densely populated area with a lot of traffic accidents, the insurance company is going to adjust your rate to account for this risk (i.e. higher price!). It works on the flip side too, if you live in an area with fewer accidents then you will more than likely get a better rate, especially if it’s a competitive marketplace. The other two questions, your real name and date of birth are used for various rating factors, including age and and credit score.

The usage of credit as a rating factor has been an ongoing practice for years, except in those states where it is not allowed. You might ask yourself, how does having bad credit have anything to do with my driving habits? The answer: nothing! However, statistics show people with lower credit scores are more likely to file a claim than those with good credit. This is simply a numbers game and one that is difficult to rationalize unless you’re an insurance company. In theory, this would be the same as charging people who liked chocolate ice cream over vanilla, because statistics showed they were poorer drivers.

What we’re seeing here is a what happens when insurance companies use correlating data as a way to determine rates. It’s been done this way for years and it’s not going away anytime soon. I often explain this to my clients and I feel a bit sad that it works this way. I was 16 once, I had to pay those exorbitant rates until I started getting in my mid twenties. This is clearly not “fair” but what other alternatives are there?

Enter Snapshot® from Progressive. Essentially, Snapshot® is a device you plug into your car’s diagnostic port and it looks at certain driving habits that often lead to accidents. It calculates how many miles you drive, the time of day you drive, and how many hard stops you make each day. If you average well in those three areas, you get a discount added to your insurance, up to 30 percent! What’s so neat about this discount is it allows people to participate in their rate instead of being at the mercy of their peer’s driving habits. You might have a 17 year old driver paying $900 for six months of coverage on a car that is used only to drive to school and back each day. With Snapshot®, he could save up to $270 just by plugging in the device. After the six months, he will the discount permanently and send back the device. There is no way that it can raise your rate and of course, it’s a completely voluntary discount.

So, if you don’t like paying for bad drivers because they are the same age, gender, and live in the same zip code as yourself, you might want to checkout Snapshot®. It’s also great for those weekend only vehicles because they will more than likely get the full 30 percent discount. For more information, talk to your local independent agent.