Advantages of Database Snapshot

Question

Can someone please highlight the advantages of Database Snapshot in SQL2008.

I read some where that

"When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent to the current size of the data files in the source database. Instead, SQL Server takes advantage of an operating system feature called sparse files. A sparse file is essentially an entry in the file allocation table and consumes almost no space on disk."

Can someone please also tell me how can a file created with no space allocated.

Thanks

Answer 1

A snapshot is great for these scenarios where you might have used a database backup in the past:

• before doing database schema upgrades
• before running adhoc data manipulation queries
• maintaining a historical snapshot you might want to analyze later
• materializing data on standby servers

In some of the above scenarios, a transaction is a better option to automatically roll back from problems, but there are sometimes cases where the query might succeed but have unintended/unforeseen consequences, so a snapshot is a good standby option to revert a bad SQL script. Of course, if this is a live system with commits occurring actively, then you wouldn't be able to go back to the snapshot without losing any commits that occurred since the snapshot was made(another reason transactions are better protection when running sql scripts against a live system).

Since the snapshot occupies less disk space, and are faster to create than a backup, they are a better option than backups in these cases.

You would still use traditional database backups to protect against disk array failures, malware, natural disasters, etc.

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/

Answer 2

"Can someone please also tell me how can a file created with no space allocated."

The files are created, but initially have no data. The snapshot mechanisms waits until a page is changed in the database and then writes the old page to the snapshot. So essentially when you are querying a snapshot you are actually querying the database itself, and only any changed pages are read from the snapshot (the old version of the page is in the snapshot).