Last week, I took a Caribbean cruise as a much needed family vacation. It was very enjoyable! We took a lot of pictures or we could say snapshots, which help us to remember those “good” times. Then, I came back to reality …
As I thought about the way pictures capture the moment, it made me think about the way we can use snapshots with technology. For example, we can take snapshots of virtual servers, snapshots of storage devices, and even snapshots on databases. In this post, I am going to explain what database snapshots are, why we might use them, and how to use them (https://technet.microsoft.com/en-us/library/ms175158.aspx).
How do database snapshots work?
A database snapshot provides a read-only, static view of a source database as it existed at the time that the snapshot was created. They are dependent on the source database and must be on the same SQL Server instance as the source database. They operate at the data-page level. When a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. These copied original pages are stored in one or more sparse files. Initially, a sparse file is essentially an empty file that contains no data. As more and more pages are updated in the source database, the size of the file grows.
Why might you use a database snapshot?
- Capturing a point in time copy of your database.
- Offloading reporting when using database mirroring.
- Safeguarding against user errors.
- Data protection before updating the database.
- Managing a test database.
How do you use database snapshots?
How do you create a database snapshot?
CREATE DATABASE [DatabaseSnapshotName] ON (NAME = 'LogicalFileName', FILENAME = 'C:\OSFilePath\DatabaseSnapshotName_LogicalFileName.ss'), AS SNAPSHOT OF [DatabaseName]
How do you revert (restore) the database to the snapshot?
RESTORE DATABASE [DatabaseName] FROM DATABASE_SNAPSHOT = 'DatabaseSnapshotName'
How do you drop a database snapshot?
DROP DATABASE [DatabaseSnapshotName]
As you can see, it is quite easy working with database snapshots! Also, since they can capture that picture of your database, there are many ways in which they can be used.
- Post detailing some problems you may encounter when using database snapshots (http://www.sqlskills.com/blogs/paul/database-snapshots-when-things-go-wrong/)