T-SQL 备份和还原

核心提示： Code/*Author:Terry.Sai.M.J 浪客 Location:BeiJingDateTime:GETDATE()Description:恢复到特定的备份日期*/IF DB_ID('db') IS NOT NULL DROP DATABASE db;GOCREATE

Code

/*
Author:Terry.Sai.M.J 浪客
Location:BeiJing
DateTime:GETDATE()
Description:恢复到特定的备份日期
*/

IF DB_ID('db') IS NOT NULL
　　　 DROP DATABASE db;
GO

CREATE DATABASE db;
GO

CREATE TABLE db.dbo.T(ID INT PRIMARY KEY IDENTITY(1,1));
GO

BACKUP DATABASE db TO DISK='c:\1.bak' WITH FORMAT;
GO

INSERT INTO db.dbo.T DEFAULT VALUES;
GO

WAITFOR DELAY '00:00:01';

DECLARE @Datetime BINARY(128);
SET @Datetime=CAST(GETDATE() AS BINARY(128));
SET CONTEXT_INFO @Datetime
GO

INSERT INTO db.dbo.T DEFAULT VALUES;
GO

BACKUP LOG db TO DISK='c:\2.bak' WITH FORMAT;
GO

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE;
GO

DECLARE @Now DATETIME;
SET @Now=DATEADD(SECOND,-1,(SELECT CAST(CONTEXT_INFO() AS DATETIME)));
--SET @Now=DATEADD(MS,-20,(SELECT CAST(CONTEXT_INFO() AS DATETIME)));

RESTORE LOG db FROM DISK='c:\2.bak' WITH RECOVERY,STOPAT=@Now;
GO

SELECT * FROM db.dbo.T

DROP DATABASE db;
GO

Code

/*
Author:Terry.Sai.M.J 浪客
Location:BeiJing
DateTime:GETDATE()
Description:使用日志恢复数据库
*/

IF DB_ID('db') IS NOT NULL
　　　 DROP DATABASE db;
GO

CREATE DATABASE db;
GO

CREATE TABLE db.dbo.T(ID INT PRIMARY KEY IDENTITY(1,1));
GO

BACKUP DATABASE db TO DISK='c:\1.bak' WITH FORMAT;
GO

INSERT INTO db.dbo.T DEFAULT VALUES;
GO

BACKUP LOG db TO DISK='c:\2.bak' WITH FORMAT;
GO

INSERT INTO db.dbo.T DEFAULT VALUES;
GO

BACKUP LOG db TO DISK='c:\3.bak' WITH FORMAT;
GO

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH RECOVERY,REPLACE;
GO

SELECT * FROM db.dbo.T
GO

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE;
GO

RESTORE LOG db FROM DISK='c:\2.bak' WITH RECOVERY;
GO

SELECT * FROM db.dbo.T
GO

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE;
GO

RESTORE LOG db FROM DISK='c:\2.bak' WITH NORECOVERY;
GO

RESTORE LOG db FROM DISK='c:\3.bak' WITH RECOVERY;
GO

SELECT * FROM db.dbo.T
GO

DROP DATABASE db;