Database snapshot of SQL Foundation

1. Understanding Snapshots

　Like a name, a database snapshot can be understood as a photo of a database moment, which records the data information of the database at this time. If you want to know the nature of snapshots, you should understand how snapshots work. When we execute T-SQL to create a snapshot, one or more sparse files are created at this time. The number of sparse files is equal to the number of database data files and must be equal, otherwise an error will be given. At this point, the sparse file is just an empty file and no space is allocated on disk to store user data, and if the database does not have any updates then the snapshot will always be an empty file. The only write-once data for a snapshot occurs when the data page of the database is updated for the first time, and the snapshot copies the data in the data page to the snapshots and really allocates space on disk. It doesn't matter how the data page changes in the future, so the other pages copy the data from the page to the snapshot before the data is first updated, until all the pages of the database are copied to the snapshot. This is not a good result, of course, because such snapshots take up a lot of space. In addition, snapshots do not have redundant storage, so in order to ensure that the database is not error should also consider multi-faceted such as using Backup.

After a snapshot is created, if the data page is not updated when the data is accessed in the snapshot, the source database is accessed directly and the snapshot is accessed if the data page is updated. Knowing how the snapshot works the next question is what the snapshot does and when to use it. The role of the snapshot is mainly 2 points: Because the snapshot can save data at a time of the database information, so the snapshot can be used for reporting; Because the snapshot holds the database's data, it restores the database to the state it was when the snapshot was created, so snapshots can be used as a security and reliability policy. Recovering a database using a snapshot is generally much faster than a backup, but the restored database will no longer be able to roll forward operations. However, there is a way to solve this shortcoming, we can compare the source database to the table in the database snapshot, the source database mistakenly deleted or the data error table can be based on the table in the snapshot to create a new data table, and the snapshot of the data are all copied to the new table created in the source database. This approach is pretty good when the table doesn't have a lot of data.

2. Sparse files

A sparse file is a way of storing files, and when it is created, sparse files consume very little disk space, and user data does not consume disk space. Although this file has been created but not allocated full storage space, as the user data is written to the sparse file to begin to take up disk space, when the properties in Windows view the file, it will find that the size of the snapshot is the size of the source database. Sparse files increase in increments of 64KB, which means that the size of the sparse file must be an integer multiple of 64KB. When you add a 64KB, it can hold 8 data pages, and this 64KB space is the first empty string or only a little bit of user data, so it is very rare. As user data grows, the sparse file will eventually be equal to the size of the source database. We can use T-SQL to view sparse file names: Select Physical_name from Sys.master_files, whereas in sys.database_files only the source database name is displayed, even if a select is made in a database snapshot. You can also use the Sys.dm_io_virtual_file_stats function to return the Size_on_disk_bytes column in the table to see the true size of the sparse file.

3. Conditions of limitation

For the source database, you must not delete, detach, or restore the source database during the existence of the database snapshot, and do not delete the source database and the snapshot. However, you can back up the database at this point, where you can see that the database backup is not affected by the snapshot. Because data pages in the source database are written to the snapshot the first time they are updated, this will inevitably affect performance when updating the source database. The source database must be online and the source database cannot be configured to scale the shared database. If you create a database snapshot in the mirrored database, the database must be in a synchronized mirroring state.

For a database snapshot, the snapshot must be created and retained on the same server instance as the source database, and if the snapshot runs out of disk space or encounters other errors when the snapshot copies the source data page, the snapshot is considered to be a suspect snapshot and the snapshot must be deleted. It is forbidden to create a snapshot of the model database, the master database, and the tempdb database, and the snapshot as a read-only file must not change any of the specifications of the DB snapshot, and of course you cannot delete the files in the database snapshot. Database snapshots cannot be backed up or restored, and database snapshots cannot be attached and detached. Because the sparse files that the database snapshot uses are provided by the NTFS file system, you can only create snapshots on the NTFS file system. Database snapshots inherit the security constraints of the source database when the snapshot is created, but the binding of the inheritance cannot be modified because of the read-only nature of the snapshot. You can only target the primary database file in a log shipping configuration, and you cannot create a database snapshot for the secondary database. When you switch from a primary server instance to a secondary server instance, you must first delete the DB snapshot. Database snapshots cannot be configured to scale a shared database, the DB snapshot does not support the FILESTREAM filegroup, and if the FILESTREAM filegroup exists in the source database, the filegroup is taken offline in the database snapshot and the database snapshot cannot be used to recover the database.

The snapshot always reflects the state of the filegroup at the time the snapshot was created, the online filegroup remains online, and the offline filegroup remains offline, which is the nature of the snapshot and filegroup state relationship. When the snapshot is first created, if the source database has an offline filegroup, because the sparse file cannot store the offline filegroup, the offline filegroup in the source database will be offline in the snapshot. After a snapshot is created, the source database cannot bring the offline filegroup online, so that the offline filegroup remains offline. The reason for an online failure is to have the file associated with the file being restored, and the source database must not be deleted, detached, or restored during the existence of the database snapshot. If the source filegroup is online when the snapshot is created, then if the data file is offline, it will remain online in the database snapshot, that is, the online filegroup remains online. Then if the data in the snapshot is accessed, it is obvious that if the source database data page is not updated that will access the source database, but the filegroup where the data page resides is offline, resulting in an access error that results in a failure.

4. Operation Snapshot

when you create a DB snapshot, MSDN recommends that you create multiple db snapshots of the source database that represent the state of the database at different times. And we should update the database snapshot periodically to improve the correctness of the source database. In the recovery database operation, other database snapshots must be deleted before recovery, and if the source database contains read-only or compressed files, the source database will not be able to perform the database restore operation if the online files are taken offline after the snapshot is created. Any user who has restore database permissions on the databases can recover the database snapshot, but be aware that the recovery process overwrites the old log file and rebuilds the log in addition to the data page recovery. This makes it impossible to roll forward the restored source database, and all data after the snapshot is lost.

--the only way to create a DB snapshot is to use T-SQL--corresponding to the normal database, the user who can create the database can create the corresponding database snapshot--corresponds to a mirrored database, only members of the sysadmin fixed server role can createCreate DatabaseTestdb_ss on(Name=Testdb_data,--The data file name used by the snapshotFileName='E:\07_ Code Storage \TESTDB_SS.SS' --the path where the snapshot is stored) asSnapshot ofTestDBGo--recovering the source database from a database snapshot, restoring the completed database retains the permissions and configuration of the database snapshot--During the recovery process, both the snapshot and the source database will be unavailable. If an error occurs during recovery, the database will retry the recovery operation after restarting. Restore DatabaseTestDB fromDatabase_snapshot='TESTDB_SS.SS'--Delete the database snapshot, and all user connections to the snapshot will be closed after the operation is complete, obviously the sparse file will not existDrop DatabaseTestdb_ss

Database snapshot of SQL Foundation