SQL Server 2005 databases Snapshot (Database Snapshot)

server| Data | database

A database Snapshot (atabase snapshot) is a read-only, static database view. A database can have multiple database snapshots, and each database snapshot will persist until it is explicitly deleted. The database snapshot remains consistent with the source database snapshot being created, so it can be used to make some reports. And because of the existence of database snapshots, we can easily restore the database to the snapshot creation time.

Database snapshots provide an effective way to restore a database to a specific point in time. A database snapshot records all transactions that have been committed since the database snapshot was created, so that you do not send out "If God can give me another chance, I ..." "The Feeling. Since it is only a record of changes to the database and not the state of the database at the current moment, the database file is not very large, as in the following example:

--Let's first create a database snapshot for database NORTHWIND, named northwind_dbss1200, and have the files for this database snapshot stored in the C:\NORTHWIND_DATA_1200.SS file

CREATE DATABASE northwind_dbss1200 on

(NAME = NORTHWIND, FILENAME =

' C:\NORTHWIND_DATA_1200.SS ')

As SNAPSHOT of NORTHWIND;

Go

--You can see the properties of this database snapshot file, as follows: You can see that the size on disk is now 128K

Use NORTHWIND

Go

--Now the Northwind database is in the process of updating

UPDATE DBO. CUSTOMERS

SET companyname= ' Newegg. COM '

--you can see that the size on disk is now 384K

--Look at the content stored in the updated column in the Northwind database that has been updated

SELECT DISTINCT COMPANYNAME from NORTHWIND. Dbo. CUSTOMERS

--Look at the content stored in the updated column in the NORTHWIND_DBSS1200 database or the previous content being updated

SELECT DISTINCT COMPANYNAME from northwind_dbss1200. Dbo. CUSTOMERS

--if an error damages A database, your may CHOOSE to REVERT the database to A database SNAPSHOT that predates the error. Reverting overwrites the ORIGINAL SOURCE database with the reverted database.

RESTORE DATABASE NORTHWIND from

Database_snapshot = ' northwind_dbss1200 '

Go

--Confirm

SELECT DISTINCT COMPANYNAME from NORTHWIND. Dbo. CUSTOMERS

--Delete database snapshots

DROP DATABASE northwind_dbss1200

Recovering a database from a database snapshot to the moment the snapshot was created