SQLServer2005数据库快照的使用与管理

-------------------------------------------创建数据库 DemoDB
CREATE DATABASE DemoDB
ON PRIMARY
(NAME='DemoDB_data', FILENAME='C:\SQLData\DemoDB_data.mdf', SIZE=5MB, MAXSIZE=10MB)
LOG ON
(NAME='DemoDB_log', FILENAME='C:\SQLData\DemoDB_log.ldf', SIZE=2MB, MAXSIZE=10MB)
GO
--------------------------------------------在 DemoDB 中创建数据表 T1 和 T2
USE DemoDB
CREATE TABLE T1 (ID INT, Name CHAR(8), Address CHAR(13))
GO
CREATE TABLE T2 (ID INT, Name CHAR(8), Address CHAR(13))
GO
--------------------------------------------在 DemoDB 的 T1 和 T2 表中插入数据
USE DemoDB
INSERT INTO T1 VALUES (1, 'Jacky', 'Suzhou')
INSERT INTO T1 VALUES (2, 'Hellen', 'Shanghai')
INSERT INTO T2 VALUES (1, 'Tom', 'Beijing')
INSERT INTO T2 VALUES (2, 'Alice', 'Hangzhou')
GO
--------------------------------------------为 DemoDB 数据库创建数据库快照 DemoDB_dbsnapshot_200510201600
CREATE DATABASE DemoDB_dbsnapshot_200510201600
ON
(NAME='DemoDB_data', FILENAME='C:\SQLSnapshot\DemoDB_dbsnapshot_200510201600.mdf')
AS SNAPSHOT OF DemoDB
GO
---------------------------------------------在数据库快照和原数据库中查询 T1 和 T2 表
USE DemoDB_dbsnapshot_200510201600
SELECT * FROM dbo.T1
SELECT * FROM dbo.T2
GO
USE DemoDB
SELECT * FROM dbo.T1
SELECT * FROM dbo.T2
GO
--------------------------------------------------
USE DemoDB
UPDATE T1 SET Name='Tony' WHERE ID=1 --在 DemoDB 中更新数据
GO
DELETE FROM T1 WHERE ID=2 --在 DemoDB 中删除数据
GO
DROP TABLE T2 --删除 T2 表
GO
--------------------------------------------------在数据库快照和原数据库中查询 T1 和 T2 表
USE DemoDB_dbsnapshot_200510201600
SELECT * FROM T1
SELECT * FROM T2
GO
USE DemoDB
SELECT * FROM T1
SELECT * FROM T2
GO
-------------------------------------------------使用数据库快照还原 DemoDB 数据库中 T1 表误删除和更新的数据
UPDATE DemoDB.dbo.T1
SET Name=(SELECT Name FROM DemoDB_dbsnapshot_200510201600.dbo.T1 WHERE ID=1) WHERE ID=1
GO
INSERT INTO DemoDB.dbo.T1
SELECT * FROM DemoDB_dbsnapshot_200510201600.dbo.T1 WHERE ID=2
GO
-----------------------------------------------------------使用数据库快照还原 DemoDB 数据库中误删除的 T2 表
USE DemoDB
CREATE TABLE T2 (ID INT, Name CHAR(8), Address CHAR(13)) --重新创建 T2 表
GO
INSERT INTO DemoDB.dbo.T2 SELECT * FROM DemoDB_dbsnapshot_200510201600.dbo.T2
GO
-----------------------------------------------------------在数据库快照和原数据库中查询 T1 和 T2 表
USE DemoDB
SELECT * FROM T1
SELECT * FROM T2
GO
USE DemoDB_dbsnapshot_200510201600
SELECT * FROM T1
SELECT * FROM T2
GO
-----------------------------------------------------------
注：如果需要周期性创建快照，可以创建 SQL Server 作业。
-----------------------------------------------------------在 DemoDB 中更新数据
USE DemoDB
UPDATE T1 SET Name='Funny' WHERE ID=1
GO
-----------------------------------------------------------在数据库快照和原数据库中查询 T1 和 T2 表
SELECT * FROM DemoDB.dbo.T1
SELECT * FROM DemoDB_dbsnapshot_200510201600.dbo.T1
SELECT * FROM DemoDB_dbsnapshot_200510201620.dbo.T1
-----------------------------------------------------------在 DemoDB 中更新数据
USE DemoDB
UPDATE T1 SET Name='Bob' WHERE ID=1
GO
-----------------------------------------------------------在数据库快照和原数据库中查询 T1 和 T2 表
SELECT * FROM DemoDB.dbo.T1
SELECT * FROM DemoDB_dbsnapshot_200510201600.dbo.T1
SELECT * FROM DemoDB_dbsnapshot_200510201620.dbo.T1
----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------删除第一次数据库快照
DROP DATABASE DemoDB_dbsnapshot_200510201600
-------------------------------------------使用数据库快照恢复 DemoDB 数据库
RESTORE DATABASE DemoDB FROM DATABASE_SNAPSHOT='DemoDB_dbsnapshot_200510201620'
-------------------------------------------
SELECT * FROM DemoDB.dbo.T1
SELECT * FROM DemoDB_dbsnapshot_200510201620.dbo.T1
-------------------------------------------
DROP DATABASE DemoDB_dbsnapshot_200510201620 --删除数据库快照
DROP DATABASE DemoDB --删除数据库