MS SQL Server 2005 and later versions (Enterprise Edition Only) include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.
What is Database Snapshot?
A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A database snapshot always resides on the same server instance as its source database.
How database snapshot works?
SQL Server uses original data pages for each databases until any query attempting to make some updates, before applying the changes to the data pages (which are belongs to the actual database) SQL Server copies the data page to the snapshot database. In this way SQL Server only copies required (changing any data page after snapshot creating time creates this requirement) data pages to a snapshot.
When you create a new snapshot of a big sized database SQL Server engine creates a new snapshot database in seconds. How can be happened? It is obvious that SQL Server doesn’t copying all data from source database to the snapshot database. SQL Server allocates the required space for snapshot database and links this to database. SQL Server manages snapshot as data pages, not as rows.
Benefits of Database snapshot
1. Vendor wants you to up-grade the vendor database using the SQL scripts. In case if something goes wrong you can either recover database by restoring all backup or by creating database snapshot.
2. We can recover damaged database using database snapshot. Also useful if we are planning to do major change in the source database
3. Mirroring database is always in recovering mode, to read that database we can use database snapshot.
4. They are fast! Creating a snapshot of our 100 GB database takes less than a second. A full backup is a 20 minute operation.
5. Restoring from a snapshot is typically a very quick operation as well, depending on the delta of the original database after the snapshot was taken. For the purposes of a code release, this delta is typically very small and the restore from the snapshot takes less than a minute.
6. Its a good idea to create database snapshot instead of Full backup for code deployment. Why waste 20 minutes waiting for a backup before you begin your code deployment when you have snapshots.
7. Snapshots can be used for reporting purposes.
Is a snapshot like a backup?
Well, kinda but not really. It’s like a backup in the sense that it can be used as a point in time to restore back to, but it can not be used for media recovery. This is because of how a snapshot works. The snapshot relies on the original data file(s) being intact to be usable for recovery. When you create a database snapshot you define a snapshot file for each data file in your database. Each snapshot file starts out empty, but as changes are made to the original database, the original data pages are written to this snapshot file. The preserves the original state of the data pages, and is used when you restore from a snapshot.
Some Facts about Database snapshot
1. A Database Snapshot is a read-only, static view of a database (the source database).
2. Multiple snapshots can exist on a source database and can always reside on the same server instance as the database.
3. A snapshot persists until it is explicitly dropped by the database owner.
4. It can only be created using the T-SQL
5. Snapshot stored under sparse file.
6. When we delete data from Regular/Source DB, it copies the data pages to Snapshot database.
7. Database Snapshot is available in Enterprise Edition of SQL Server.
8. Database snapshots are dependent on the source database. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy.
9. Any transactions which are uncommitted at the time you create a snapshot will not be included in the database snapshot.
10. You cannot attach or detach database snapshots
11. Database snapshots can be created only on a NTFS file system.
12. A SQL Server instance that has a snapshot created, cannot be upgraded to a new version of SQL Server.
13. Reverting to a snapshot drops all the full-text catalogs on the source database.
14. Reverting does not work in an offline or corrupted database
15. If the source database becomes unavailable, all of its database snapshots also become unavailable.
What is sparse file?
A file provided by the NTFS file system that requires much less disk space than would otherwise be needed. A sparse file is used to store pages copied to a database snapshot. When first created, a sparse file takes up little disk space. As data is written to a database snapshot, NTFS allocates disk space gradually to the corresponding sparse file.
Pros and Cons of Snapshot
1. They provide a convenient, read-only point-in-time copy of your data.
2. Initially the snapshot data files are small and are very quick to create. They should only become large if your database is subject to frequent modifications.
3. You will not be contending with blocking due to update/insert operations (let’s assume snapshot isolation is not in use).
4. Initially the snapshot data files are small.
5. There are no restrictions on creating a snapshot on a mirrored source (or destination) and mirroring is not disrupted when creating a snapshot.
6. You are prevented from restoring a snapshot whilst mirroring is active. You must first stop mirroring and once you have restored you must then restore the destination database and enable mirroring again.
7. There are no restrictions on creating a snapshot on a log shipped source (or destination) and log shipping is not disrupted when creating a snapshot.
8. You are not prevented from restoring the snapshot when log shipping is present.
1. You can not backup a snapshot so if you have to restore your source database your snapshots are lost.
2. Every update/insert transaction on the source server potentially bears the overhead of the page copy operation to maintain the snapshot(s).
3. You cannot grant a new user access to the data in a snapshot. Permissions are inherited form the source database as it existed at the time of snapshot creation.
4. Full text indexes are not available on snapshots so if you require full text searching for your reporting then snapshots are not an option.
5. If the drive hosting the snapshot runs out of space causing an update to fail, the snapshot is marked suspect and can not be recovered.
6. When you restore a snapshot all other snapshots of the database have to be deleted which would be a problem if you are maintaining reporting snapshots.
7. If your administrative error involved damage to a physical file (say a drive was accidentally wiped) the snapshot will not help you.
Click here to see, How to Create/Restore a Database Snapshot?
If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!