sqlserver 日志恢复方法(搞定drop和truncate)

–1

use master

backup database logTest

to disk=’D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak’

with format

–2

use logTest

create table table_1(Id int, test varchar(max))

insert table_1(Id,test) values (1,’3333′);

insert table_1(Id,test) values (2,’4333′);

insert table_1(Id,test) values (3,’5333′);

use master

–2.1

WAITFOR DELAY ’00:00:05′

GO

use logTest

truncate table table_1

select GETDATE()

use master

–3

use master

backup log logTest

to disk=’D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest_log.bak’

with format

–4

–DECLARE @dt datetime

–SELECT @dt=DATEADD(ms,-1800,GETDATE())

–select @dt

restore database logtest

from disk = ‘D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak’

WITH REPLACE

RESTORE LOG logTest

FROM DISK=’D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest_log.bak’

with STOPAT= ‘2009-10-26 12:38:57.133’

GO