恢复到特定的备份日期

--恢复到特定的备份日期

IF DB_ID('PRG') IS NOT NULL

DROP DATABASE PRG;

GO 

CREATE DATABASE PRG;

GO 

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

GO 

BACKUP DATABASE PRG TO DISK='d:/1.bak' WITH FORMAT;

GO 

INSERT INTO PRG.dbo.T DEFAULT VALUES;

GO 

WAITFOR DELAY '00:00:01'; 

DECLARE @Datetime BINARY(128);

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

print '---------';

print @Datetime;

print '---------';

SET CONTEXT_INFO @Datetime

GO 

INSERT INTO PRG.dbo.T DEFAULT VALUES;

GO 

BACKUP LOG PRG TO DISK='d:/2.bak' WITH FORMAT;

GO 

RESTORE DATABASE PRG FROM DISK='d:/1.bak' WITH NORECOVERY,REPLACE;

GO 

DECLARE @Now DATETIME;

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

print '^^^^^^^^^^^';

print @Now;

print '***********';

RESTORE LOG PRG FROM DISK='d:/2.bak' WITH RECOVERY,STOPAT=@Now;

GO 

SELECT * FROM PRG.dbo.T

DROP DATABASE PRG;

GO