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