Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Jan 3 '13 at 4:36

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 5 down vote accepted

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/

share|improve this answer
add comment

"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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.