SQL Server 2012 Database Snapshots Feature
This feature in SQL Server 2012 allows creation of a production database in read only form. Snapshots in SQL Server 2012 works at the data layer and simply data is copied from source to destination snapshot. Snapshot can be created for a database on the same server as the source database. Multiple snapshots can be created against a single database. Note that these are database snapshots, which is different from VSS snapshots which are used during the backup process. As mentioned earlier, this ready only copy of the database can be used to query reports while not affecting production server performance. If you need to back test the database and need to go back to point in time for reports, snapshots can be used. A lot of organizations makes a snapshot every financial quarter and save the snapshot for reporting purposes. Database snapshots are highly recommended when performing maintainence on the server. This allows database administrators to go back in time if corruption occurs. Administrator errors such as dropped table can be easily fixed if you had database snapshots.
Here are a few requirements and gotchas for Database snapshot feature in SQL 2012:
- Source database cannot be offline
- You may create a snapshot from secondary copy of alwayson Availability group.
- Scalable shared database not be configured on source database.
- The source database cannot be detached or dropped while snapshot exist.
- Source database and snapshot copy must be on same SQL Server Instance.
- Snapshots are not compatible with system databases
- You may not backup or detach a snapshot.
Creating a snapshot
SQL Server management studio does not support snapshots function. This means only way to create snapshots is good old TransactSQL. To create a database snapshot in SQL Server 2012, run below:
CREATE DATABASE name_of_the_snapshot
ON
(
NAME = filename,
FILENAME = 'file_name'
) [ ,...n ]
AS SNAPSHOT OF orginalDBname
[;]
Restoring a snapshot
If your primary database, aka source database becomes corrupt, you may need to restore it from the snapshot created earlier. Note that all changes after the snapshot will be lost. If there is a data corruption, ensure that corruption occurred after the snapshot. In this case, primary database will be overwritten with the snapshot. To restore a snapshot run below command:
RESTORE DATABASE db_name FROM DATABASE_SNAPSHOT = db_snapshot_name
Deleting a snapshot
It’s ideal to delete a snapshot if no longer needed. This will free up resources and disk from the server. To delete a snapshot database, run below command:
DROP DATABASE snapshot_db_name