Database snapshot is the read only database storing ONLY the modify pages from the original database. Internally it use sparse file type, therefore it use less space compare the original database size. After the snapshot being created. Whenever the original database page being modified, sql server copy the original page to the snapshot file.
Create the snapshot. The snapshot can be created at the same server as the source database.
We can query the snapshot just like query regular database.
When access the snapshot, user actual access the source database unless those page has been updated from the source. Form this logical, all the security, file group status will be maintain as is when the time the snapshot is created. The snapshot is the read only copy. hence even user has write permission, user still can not update the table in the snapshot.
If the source database, or file group become unavailable, user may encounter issue accessing the snapshot as well. Therefore we can not treat the snapshot as type of disaster recovery solution.
We can find the snapshot from the sys.database
Find out the snapshot file size
Form Windows explorer: The size is the max size it can grow. The Size on the disk is the size of the current file.
Find current size from DMV sys.dm_io_virtual_file_stats
Find max size within the database’s sys.database_files or sys.master_files. The size number shows in the size column is in Pages. So it needs to times 8192 to get the actual byte.
21760* 8192 = 178 257 920 bytes
Lets try create table and insert rows in the source database to see how the snapshot file size grow.
As you can see when the source database changes, the snapshot file size will start increasing.
To restore (revert) the database from snapshot
- Only one snapshot can be use to restored. we have to drop all other snapshot first.
- Only can be restore to the source database, we can not restore the snapshot to the different database.
- If the source database contain FILESTREAM file group, the file group will be marked offline in the snapshot. The snapshot can not be used to revert it to the source database.
Msg 3138, Level 16, State 2, Line 2
The database cannot be reverted because FILESTREAM BLOBs are present.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Drop the snapshot
File steam storage