SQL Server的还原(2)——STOPAT

参考文献

[1]SQL Server的还原

[2]如何还原到某个时间点 (Transact-SQL)

数据库备份

在参考1中我们提到了sqlserver中数据库还原的操作，但是没有提到数据库还原中一个很有用的关键字STOPAT。下面我们就来讨论如何使用STOPAT关键字来还原数据库。

--开始：实验4：使用STOPAT关键字来还原数据-----------------------------------
--step1:创建备份设备
EXEC sp_addumpdevice 'disk', 'TESTDB2Backups', 'd:\backup\TESTDB2Backups.bak';

--step2:创建数据库TESTDB2，然后再执行下面的sql语句
USE TESTDB2
create table customers
(
    id int identity(1,1) primary key not null, 
    name varchar(15)
);

--step3:创建完整备份
BACKUP DATABASE TESTDB2 TO TESTDB2Backups
   WITH INIT;--override the device
GO
--Messages信息如下,我们可以看到完整备份的文件存放在“file 1”上面
/*
Processed 184 pages for database 'TESTDB2', file 'TESTDB2' on file 1.
Processed 4 pages for database 'TESTDB2', file 'TESTDB2_log' on file 1.
BACKUP DATABASE successfully processed 188 pages in 0.163 seconds (8.998 MB/sec).
*/

--step4：插入一条记录
insert into customers(name) values('日志1备份前');

--step5：备份事务日志1
BACKUP LOG TESTDB2     TO TESTDB2Backups;
GO
--Messages信息如下,事务日志文件存放在“file 2”上面
/*
Processed 8 pages for database 'TESTDB2', file 'TESTDB2_log' on file 2.
BACKUP LOG successfully processed 8 pages in 0.085 seconds (0.729 MB/sec).
*/

--step6：插入一条记录
insert into customers(name) values('日志2备份前');

--step7：备份事务日志2
BACKUP LOG TESTDB2     TO TESTDB2Backups;
GO
--Messages信息如下,事务日志文件存放在“file 3”上面
/*
Processed 1 pages for database 'TESTDB2', file 'TESTDB2_log' on file 3.
BACKUP LOG successfully processed 1 pages in 0.091 seconds (0.005 MB/sec).
*/

--step8：插入一条记录
insert into customers(name) values('日志3备份前');

--step9：备份事务日志3
BACKUP LOG TESTDB2     TO TESTDB2Backups;
GO
--Messages信息如下,事务日志文件存放在“file 4”上面
/*
Processed 1 pages for database 'TESTDB2', file 'TESTDB2_log' on file 4.
BACKUP LOG successfully processed 1 pages in 0.054 seconds (0.009 MB/sec).
*/

此时查询customers这张表，得到的结果是：

id          name
----------- ---------------
1           日志1备份前
2           日志2备份前
3           日志3备份前

接下来我们查看日志的详细信息，比如备份时间，LSN等。我们可以通过SSMS->database->tase->restore->database找到如下信息：

上图中，Position就是备份集的序号。通过备份集的序号，我们还可以解决SQL Server的还原中没有解决的问题，就是备份到一个备份集并还原。然后是事务日志的start date和end date。我们下面使用的stop at就需要时间来确定。

数据库还原到某一时间点（1）部分还原

接着我们执行以下的还原操作：

--step10:接下来进入还原阶段，确保当前使用的数据不是TESTDB2，
--并且断开其他所有与TESTDB2的连接，可以断开连接再执行连接
--step11：完整还原数据库
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1，这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO
--Messages信息如下
/*
Processed 184 pages for database 'TESTDB2', file 'TESTDB2' on file 1.
Processed 4 pages for database 'TESTDB2', file 'TESTDB2_log' on file 1.
RESTORE DATABASE successfully processed 188 pages in 0.222 seconds (6.607 MB/sec).
*/

--step12:根据日志信息以及备份集恢复日志到某一点，roll forward
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, --使用RECOVERY，表示使数据库恢复到一致状态
        FILE=2,--备份集为2
        STOPAT = '7/9/2012 4:12:44 PM';
GO
--Messages信息如下
/*
Processed 0 pages for database 'TESTDB2', file 'TESTDB2' on file 2.
Processed 8 pages for database 'TESTDB2', file 'TESTDB2_log' on file 2.
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 8 pages in 0.035 seconds (1.771 MB/sec).
*/

--step13:是在执行上述操作以后，TESTDB2往往处于restoring状态，使用此语句使数据库可用
RESTORE DATABASE TESTDB2 WITH RECOVERY; 
--Messages信息如下
--RESTORE DATABASE successfully processed 0 pages in 0.418 seconds (0.000 MB/sec).

此时查询customers这张表，得到的结果是：

id          name
----------- ---------------
1           日志1备份前

这是因为上面日志还原的时候STOPAT是第一次事务日志备份的end time。

数据库还原到某一时间点（2）全部还原

接下来我们执行一个完整的日志还原，执行如下命令：

--step14：完整还原数据库
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1，这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO
--Messages信息如下
/*
Processed 184 pages for database 'TESTDB2', file 'TESTDB2' on file 1.
Processed 4 pages for database 'TESTDB2', file 'TESTDB2_log' on file 1.
RESTORE DATABASE successfully processed 188 pages in 0.214 seconds (6.854 MB/sec).
*/


--step15:根据日志信息以及备份集恢复日志到某一点，roll forward
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH NORECOVERY, 
        FILE=2,--备份集为2
        STOPAT = '7/9/2012 4:12:44 PM';
GO


--step16:根据日志信息以及备份集恢复日志到某一点，roll forward
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH NORECOVERY, 
        FILE=3,--备份集为2
        STOPAT = '7/9/2012 4:13:21 PM';
GO

--step17:根据日志信息以及备份集恢复日志到某一点，roll forward
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=4,--备份集为3
        STOPAT = '7/9/2012 4:13:50 PM';
GO

--step18:是在执行上述操作以后，TESTDB2往往处于restoring状态，使用此语句使数据库可用
RESTORE DATABASE TESTDB2 WITH RECOVERY; 

此时查询customers这张表，得到的结果是：

id          name
----------- ---------------
1           日志1备份前
2           日志2备份前
3           日志3备份前

数据库还原到某一时间点（3）时间的选择

--step19：完整还原数据库
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1，这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO

--step20:根据日志信息以及备份集恢复日志到某一点，roll forward
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPAT = '7/9/2012 4:13:50 PM';
GO

--step21:是在执行上述操作以后，TESTDB2往往处于restoring状态，使用此语句使数据库可用
RESTORE DATABASE TESTDB2 WITH RECOVERY; 

在上述命令中，我们的STOPAT = '7/9/2012 4:13:50 PM';大于事务日志的end date，但是执行以后的得到的结果跟第一个查询一样，结果为：

id          name
----------- ---------------
1           日志1备份前

但是假如我们的STOPAT小于start time，则会报错，错误如下：

The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 2。

因此可以得出结论：stopat应该是大于等于日志的start time的。