SQL Server Snapshot

Recently, the development system used SqlServer2008 R2, but due to the increase of the system data pressure, ready to add a database synchronized with the official databases, used for the interface and reports to use, so began to the SQL Server in some of the technology began to study, First, let's look at the most basic database snapshots.

　　Basic concepts

The basic concept of snapshot is briefly introduced, and the database snapshot is a read-only static view of the SQL Server database (the source database). From the moment the snapshot was created, the database snapshot is transactionally consistent with the source database. A database snapshot is always on the same server instance as its source database. When the source database is updated, the database snapshot is also updated. Therefore, the longer a database snapshot exists, the more likely it is to run out of free disk space.

The level of the database snapshot operation is page level, and the original page is copied from the source database to the snapshot before the first modification of the source database page, and the snapshot stores the original page, preserving the data records at the time the snapshot was created. Repeat this procedure for each page that you want to make the first modification. for the user, the database snapshot always seems to remain the same, because the read operation on the database snapshot always accesses the original data page regardless of where the page resides. In fact, the snapshot will always back up the original page before the modification of the source data, so as the data is modified, the file storage of the snapshot will slowly become larger.

In order to store the copied original page, the snapshot uses one or more sparse files.Initially, sparse files are essentially empty files that do not contain user data and are not allocated disk space for storing user data.   as more pages are updated in the source database, the file size is growing.   illustrates the effect of two relative update modes on the size of a snapshot.   update mode A reflects an environment in which only 30% of the original pages were updated during the snapshot's lifespan.  

　　

　　Advantages of database Snapshots　　

Snapshots can be used for reporting purposes.
Clients can query database snapshots, which is useful for writing reports based on the data at the time the snapshot was created.

The impact of data protection from management errors.
If a user error occurs on the source database, you can restore the source database to the state when the given DB snapshot was created. The missing data is limited to the data that has been updated in the database since the snapshot was created.
For example, before you make a major update, such as a bulk update or schema change, create a database snapshot of the database to protect the data. Once the error has occurred, you can use the snapshot to restore the database to the state it was in when the snapshot was generated. For this purpose.

Protect data against user errors.
Creating a database snapshot on a regular basis can mitigate the impact of a significant user error (for example, a deleted table). To protect your data well, you can create a series of database snapshots that span enough to identify and handle most user errors. For example, depending on the disk resource, you can create 6 to 12 scrolling snapshots every 24 hours. Each new snapshot is created and the oldest snapshot is deleted.

To recover from a user error, you can restore the database to a snapshot of the previous time that the error occurred. Recovery for this purpose is likely to be much faster than restoring from a backup, but you will not be able to roll forward the data thereafter.
Alternatively, you can manually recreate the deleted table or other missing data using the information in the snapshot. For example, you can bulk copy the data in a snapshot into a database, and then manually merge the data back into the database.

Manage the test database.
In a test environment, it is useful to run test protocols repeatedly for the database that will contain the same data at the beginning of each round of testing. Before running the first round of tests, the application developer or tester can create a database snapshot in the test database. After each test run, the database can quickly return to its previous state by recovering the database snapshot.

　　Limitations of database Snapshots　

Limitations of the source database:

The database cannot be deleted, detached, or restored. You can back up the source database, which is not affected by the database snapshot.

The performance of the source database is affected. Because the copy-on-write operation is performed on the snapshot each time the page is updated, I/O increases on the source database.

You cannot delete files from the source database or from any snapshots.

Limitations of the Snapshot database:

The database snapshot must be created and retained on the same server instance as the source database.

Always make a database snapshot of the entire database.

When a page that is updated in the source database is forcibly pressed into the snapshot, if the snapshot runs out of disk space or encounters other errors, the snapshot becomes a suspect snapshot and must be deleted.

The snapshot is read-only.

Creating snapshots on the model database, the master database, and the tempdb database is prohibited.

You cannot create a database snapshot on a FAT32 file system or on a RAW partition. The sparse file used by the database snapshot is provided by the NTFS file system.

A database snapshot inherits the security constraints of its source database when the snapshot is created. Because the snapshot is read-only, you cannot change the inherited permissions, and the change permissions on the source database will not be reflected in the existing snapshot.

If the source database has a status of Recovery_pending, it may not be able to access its database snapshot. However, after you resolve the problem with the source database, the snapshot becomes available again.

As long as the understanding of its principle of its limitations are naturally clear.

　　To create a database snapshot

Before you create a database, you must first know that the database is distributed across several files, because snapshots need to be copy-on-writing for each file.

There are several database files to see the database first:

--Create a snapshot of the number of data files exec sp_helpdb Demo01go

My database has only one database file, so just execute the following script to create the snapshot:

--Creating snapshot Create Database Snap_demo_10amon (name=demo01,filename= ' C:\SQLTest\SNAPSHOT\Snap_advlt.snap ') as SNAPSHOT of Demo01go

If the database exists in a filegroup, there may be examples of multiple database file creation snapshots:

----Multi-file data creation snapshot create DATABASE sales_snapshot1200 on (NAME = spri1_dat, FILENAME = ' C:\Program Files\Microsoft SQL Server\ms SQL11. Mssqlserver\mssql\data\spri1dat_1200.ss '), (NAME = spri2_dat, FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11 . Mssqlserver\mssql\data\spri2dt_1200.ss '), (NAME = sgrp1fi1_dat, FILENAME = ' C:\Program Files\Microsoft SQL Server\ MSSQL11. Mssqlserver\mssql\data\sg1fi1dt_1200.ss '), (NAME = sgrp1fi2_dat, FILENAME = ' C:\Program Files\Microsoft SQL Server\ MSSQL11. Mssqlserver\mssql\data\sg1fi2dt_1200.ss '), (NAME = sgrp2fi1_dat, FILENAME = ' C:\Program Files\Microsoft SQL Server\ MSSQL11. Mssqlserver\mssql\data\sg2fi1dt_1200.ss '), (NAME = sgrp2fi2_dat, FILENAME = ' C:\Program Files\Microsoft SQL Server\ MSSQL11. MSSQLSERVER\MSSQL\DATA\SG2FI2DT_1200.SS ') as SNAPSHOT of Salesgo

　　

　　Database snapshot Recovery and deletion

　　

　　

SQL Server Snapshot