Database Snapshot

Database Snapshot

System Restore Point: In Microsoft Window Operating system , user can create a restore point just point in time. That feature allow user to revert to the status of computer system at point in time restore point created w.r.t system files, application installed, window registry and setting. 

If a developer/dba requires such thing in term of sql server database, Then Database Snapshot. 

What is it?: 

       Database snapshot is static and read only view of a database. A Database can have multiple snapshots. Database snapshot can be created on any drive partition. The Database snapshot cannot take size on disc as the Database size.

Snapshot can be used for reporting purposed. In case of user error on source database, revert the source database can be performed using snapshot in state when snapshot was created.

Reason why to take Database Snapshot?:

• Historical Data: To generate reports based on historical data. As it record data at point in time , it is created. Suppose you require reports quarterly, monthly basis , you can create database snapshots quarterly , monthly basis and can query to generate report on that database snapshots instead on real database. i.e to investigate a department performance.
• Mirror Database: Using Database snapshots with database mirroring permits to make the data on mirror server accessible for reporting.
• Safeguard data against administrative error: Before doing bulk update, create a database snapshot on database. In case of any mistake done, you can use snapshot to recover by reverting the database to the snapshot. Example: Once one of my team members was performing a update on production database server, he forgot out where clause in update command that causes the whole day goes waste in reverting data using various select operation. In that time, we didn't have any database snapshot then we can revert the database and with minimal action we should be running db...We can also recover a single database object from snapshot.
• Safeguard data against user error: Accidental attract on database such as drop table. 
• Maintaining test database: Create a Database snapshot and perform testing of application. After successful testing do revert database, no test data will remain in database. Mostly we don't care for that case as well.

Creating Database Snapshot:

Query to generate Database Snapshot.

       

You should require enterprise Edition of Sql Server.As Soon as query execute the Database name MYBlog_04042015 will appear under Database Snapshots with its all object.

File size of the Database Snapshot be very small as comparable to the Source Database.

Dropping Database Snapshot:

Any user with DROP database permission can perform drop a database snapshot. Command is same as dropping datatabase

Drop Database [DatabaseSnapshotName]

Drop Database MYBlog_04042015

 Dropping a database snapshot deletes the database snapshot from SQL Server and deletes the sparse files that are used by the snapshot. When you drop a database snapshot, all user connections to it are terminated

Limitations:

Limitation on Source Database:

As log as a database snapshot exists, the following limitations exist on the snapshot's source database:

• The database cannot be dropped, detached, or restored. Backup of database work normally. There is not imparting on backup of a database.
• The Source Database must be online
• The performance gets reduced due to increased I/O on the source db resulting from a copy on write protection. Every time a page is updated.
• To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.

Limitation on Snapshot Database:

The following limitations apply to database snapshots:

• A database snapshot must be created and remain on the same server instance as the source database. 
• Snapshots are read-only.
• Snapshots of the model, master, and tempdb databases are prohibited.
• You cannot drop files from a database snapshot.
• You cannot back up or restore database snapshots.
• You cannot attach or detach database snapshots.
• Database snapshots always work on an entire database. 
• You cannot change any of the specifications of the database snapshot files.
• Because database snapshots are not redundant storage, they do not protect against disk errors or other types of corruption. Taking regular backups and testing your restore plan are necessary to protect a database. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that. 
• When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted. 
• You cannot create database snapshots on FAT32 file system or RAW partitions. The sparse files used by database snapshots are provided by the NTFS file system.
• Full-text indexing is not supported on database snapshots. Full-text catalogs are not propagated from the source database. 
• A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots. 
• A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, and offline filegroups remain offline. For more information, see "Database Snapshots with Offline Filegroups" later in this topic.
• If a source database becomes RECOVERY_PENDING, its database snapshots may become inaccessible. After the issue on the source database is resolved, however, its snapshots should become available again.
• Reverting is unsupported for read-only filegroups and for compressed filegroups. Attempts to revert a database containing either of these types of filegroups fail. For more information on reverting
• In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.
• A snapshot cannot be configured as a scalable shared database. 

Conclusion: Database Snapshots is a very good way for safeguard against administrative error , user/developer error.You can use the Database snapshot in development environment as well to track daily object changes.

~

Sunit Kan