基于时间点恢复数据库stopat

create database newtestdb

use newtestdb
go

drop table t1
go

create table t1 (
id int not null identity(1,1) primary key
,vdate datetime default (getdate())
,name varchar(32)
)

 

backup database newtestdb to disk='c:\newtestdb_ful.bak'
insert into t1 (name) values ('aa')

 

insert into t1 (name) values ('bb')
backup log newtestdb to disk='c:\newtestdb_1.trn' --2016-05-18 13:42:30.767
select getdate()

insert into t1 (name) values ('cc')
backup log newtestdb to disk='c:\newtestdb_2.trn' --2016-05-18 13:43:59.707
select getdate()

insert into t1 (name) values ('dd')
backup log newtestdb to disk='c:\newtestdb_3.trn' --2016-05-18 13:45:05.310
select getdate()

insert into t1 (name) values ('ee')
backup log newtestdb to disk='c:\newtestdb_4.trn' --2016-05-18 13:46:29.783
select getdate()

insert into t1 (name) values ('ff')
backup log newtestdb to disk='c:\newtestdb_5.trn' --2016-05-18 13:47:21.833
select getdate()

--恢复到dd
use master
go
restore database newtestdb from disk='c:\newtestdb_ful.bak' with replace,norecovery;
/*
Processed 344 pages for database 'newtestdb', file 'newtestdb' on file 1.
Processed 6 pages for database 'newtestdb', file 'newtestdb_log' on file 1.
RESTORE DATABASE successfully processed 350 pages in 0.025 seconds (109.179 MB/sec).
*/
restore log newtestdb from disk='c:\newtestdb_1.trn' with replace,norecovery,stopat='2016-05-18 13:45:09.310';
/*
Processed 0 pages for database 'newtestdb', file 'newtestdb' on file 1.
Processed 7 pages for database 'newtestdb', file 'newtestdb_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 7 pages in 0.008 seconds (6.103 MB/sec).
*/
restore log newtestdb from disk='c:\newtestdb_2.trn' with replace,norecovery,stopat='2016-05-18 13:45:09.310';
/*
Processed 0 pages for database 'newtestdb', file 'newtestdb' on file 1.
Processed 1 pages for database 'newtestdb', file 'newtestdb_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1 pages in 0.006 seconds (0.325 MB/sec).
*/
restore log newtestdb from disk='c:\newtestdb_3.trn' with replace,norecovery,stopat='2016-05-18 13:45:09.310';
/*
Processed 0 pages for database 'newtestdb', file 'newtestdb' on file 1.
Processed 1 pages for database 'newtestdb', file 'newtestdb_log' on file 1.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 1 pages in 0.006 seconds (0.325 MB/sec).
*/
restore log newtestdb from disk='c:\newtestdb_4.trn' with replace,norecovery,stopat='2016-05-18 13:45:09.310';
/*
Processed 0 pages for database 'newtestdb', file 'newtestdb' on file 1.
Processed 1 pages for database 'newtestdb', file 'newtestdb_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.004 seconds (0.488 MB/sec).
*/
restore log newtestdb from disk='c:\newtestdb_5.trn' with replace,norecovery,stopat='2016-05-18 13:45:09.310';
/*
Msg 4305, Level 16, State 1, Line 57
The log in this backup set begins at LSN 34000000022700001, which is too recent to apply to the database. An earlier log backup that includes LSN 34000000022500002 can be restored.
Msg 3013, Level 16, State 1, Line 57
RESTORE LOG is terminating abnormally.
*/

restore database newtestdb with replace,recovery;
/*
RESTORE DATABASE successfully processed 0 pages in 0.086 seconds (0.000 MB/sec).
*/

 

--成功恢复至dd

select * from newtestdb.dbo.t1

id vdate name
1 2016-05-18 13:41:44.500 aa
2 2016-05-18 13:42:26.767 bb
3 2016-05-18 13:43:57.707 cc
4 2016-05-18 13:45:05.290 dd