亂馬客

USE master
go

--建立測試的DB
CREATE DATABASE MyDB ON 
(NAME = MyDB1, FILENAME = N'c:\Mydb1.mdf'),
(NAME = MyDB2, FILENAME = N'c:\Mydb2.ndf')
go

USE MyDB
go

--建立測試Table T1
CREATE TABLE T1 ( c1 INT );
go

--新增資料
DECLARE @i INT;
SET @i = 0;
WHILE @i < 1000
    BEGIN
        INSERT  INTO T1
        VALUES  ( @i )
        SET @i = @i + 1;
    END;
SELECT  COUNT(*)
FROM    T1;


--DROP DATABASE  MyDB_SS

--建立MyDB的SNAPSHOT
CREATE DATABASE MyDB_SS ON
(NAME = MyDB1, FILENAME=N'c:\Mydb1xx.ss'),
(NAME = MyDB2, FILENAME = N'c:\Mydb2xx.ss') 
AS SNAPSHOT OF MyDB;

--各有1000筆資料
SELECT  COUNT(*)
FROM    MyDB..T1;
SELECT  COUNT(*)
FROM    MyDB_SS..T1;

--把MyDB的T1給刪掉
TRUNCATE TABLE T1;

--將資料從SNAPSHOT Restore回來
USE master
go

RESTORE DATABASE MyDB 
FROM DATABASE_SNAPSHOT = 'MyDB_SS';
 
--資料回來了，各有1000筆資料
SELECT  COUNT(*)
FROM    MyDB..T1;
SELECT  COUNT(*)
FROM    MyDB_SS..T1;

--練習結束，刪除資料庫
DROP DATABASE MyDB_SS;
DROP DATABASE MyDB;