- A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation.
- Database Snapshots are a new Enterprise Edition-only tool, introduced in SQL 2005. Uncommitted transactions are rolled back in a newly created database snapshot.
- Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation.
What is Copy-on-Write?:
What is the Size of Newly Created Database Snapshot:
- The snapshots of a database must be on the same server instance as the database. Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.
- To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
- If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database
- As long as a database snapshot exists, the following limitations exist on the snapshot's source database:
- The database cannot be dropped, detached, or restored.
- Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
- Files cannot be dropped from the source database or from any snapshots
- You can not backup a snapshot so if you have to restore your source database your snapshots are lost.
- 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 and subsequent changes to security in the source database do not filter down to the snapshots.
- Database snapshots are not redundant storage, and as a result do not provide any protection against disk errors or other types of corruption.
Reverting Original Database incase of Error:
If you make a mistake, any user who has RESTORE DATABASE permissions on the source database can revert it to its state by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.
Note: 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. Performing all your scheduled backups remains essential. 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.
Reverting to a snapshot drops all the full-text catalogs. The revert operation then overwrites the old log file and rebuilds the log. The metadata of a reverted database is the same as the metadata at the time of the snapshot.
Reverting is unsupported under the following conditions:
- The source database contains any read-only or compressed filegroups.
- Any files are offline that were online when the snapshot was created.
- More than one snapshot of the database currently exists.
- Only the snapshot that you are reverting can exist..
During the revert operation, both the snapshot and the source database are unavailable. The source database and snapshot are both marked as "In restore." If an error occurs during the revert operation, it will try to finish reverting when the database starts up again.
The reverted database retains the permissions and configuration (such as database owner and recovery model) of the database snapshot
Note:
We Cannot use SQL Server Management Studio (SSMS) to create snapshots.
More Info:
Database Snapshots
Beside this option there is also another new option in SQL 2005 Called Partial Backups.
Partial Backups
Partial backup contains all the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.
Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup of a read-only database contains only the primary filegroup.
We can also use Differential Partial Backups to take differential data since last partial backup.
Seems like "File and Filegroup" Backup Option in SQL 2000 but the difference is here no need to specify the file group name since by default it takes backup of all filegroups which are not read-only.
More Info:
Partial Backups
Copy-Only Backups:
it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database but how to do that?
Copy-Only backup Option introduced in SQL 2005 is the option for this.
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored
Syntax:
BACKUP LOG database_name TO
More Info:
Copy-Only Backups
