SQLserver数据备份与恢复

?

先前做了数据库的备份程序，现在要做一个恢复功能，这样，每天生成备份后，恢复到备份机器上。可以随便检测。

?

四、数据恢复

1、Restore vs. Recovery

Restore和Recovery是两个不同的概念，但在数据恢复过程中又是紧密联系的。

Restore相当于从备份集中重建整个或者部分数据库，Restore是无法改变数据库状态的，如脱机和联机等。

Recovery则是将数据库从脱机状态恢复到联机状态中供用户使用。Recovery在SQL Server启动时也会发生，在数据库启动过程中，SQL Server会检查事务日志，看是否存在已提交或未提交的事务，如果发现在最后一次检查点发生后，还有已提交的事务，则SQL Server会对这些事务进行REDO(ROLL FORWARD)；而如果发现未提交的事务，则进行UNDO（ROLL BACK）。

一旦对数据库进行了Recovery，则将无法再进行Restore操作。

Recovery事实上是Restore的一个选项，默认情况下，进行Restore操作时，SQL Server还会进行Recovery操作。在单独对全备份进行恢复时，可以不用考虑Recovery，但如果后续仍有日志备份或差异备份需要恢复，则必须注意Recovery选项的选择。

数据库恢复的语句如下：

?

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     mydb 
    
    FROM
    
     mydbdevice 
    
    WITH
    
     RECOVERY
   
   

显示指定RECOVERY 或者NORECOVERY是个良好的习惯。

2、在RECOVERY过程中的可用性

一般情况下，如果SQL Server处于Recovery过程中时，用户是无法使用数据库的。但可以通过设置fast recovery使得用户在Recovery中使用数据库。

在所有已提交的事务都被ROLL FORWARD后，数据库就是联机状态。

一般情况下，SQL Server强制已提交的事务写入数据库中，这个过程叫做检查点（CHECKPOINT）。可以通过强制使用CHECKPOINT命令来触发，默认则是以RECOVERY INTERVAL为周期，可以通过SP_CONFIGURE命令来设置RECOVERY INTERVAL。

   
   
    
    sp_configure 
    
    '
    
    Show Advanced Options
    
    '
    
    ,
    
    1
    
     sp_configure 
    
    '
    
    recovery interval
    
    '
    
    ,
    
    5
    
     
    
    RECONFIGURE
    
     
    
    WITH
    
     OVERRIDE sp_configure 
    
    '
    
    Show Advanced Options
    
    '
    
    ,
    
    0
   
   

默认情况下，RECOVERY INTERVAL 设置为0，表示SQL Server根据需要动态管理CHECKPOINT的发生。

3、备份文件里面的信息

在primary文件里，存储着与数据库结构有关的一些信息，如文件位置等。因此备份后，这些信息同样保留在备份文件里面。如果要恢复到不同的磁盘上或服务器后，需要进行额外的更改。

在SQL Server里，提供了 RESTORE HEADERONLY,RESTORE FILELISTONLY,RESTORE LABELONLY,RESTORE VERIFYONLY 等命令来读取备份文件的信息。也可以通过MSDB里的表来获取备份集的相关信息。
A．RESTORE HEADERONLY

   
   
    
    RESTORE
    
     HEADERONLY 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
   
   

各字段的含义可以查看联机丛书。
B.RESOTRE FILELISTONLY

   
   
    
     
    
    RESTORE
    
     FILELISTONLY 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
   
   

C.RESOTRE LABELONLY

   
   
    
     
    
    RESTORE
    
     LABELONLY 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
   
   

?

D.RESOTORE VERIFYONLY

?

   
   
    
     
    
    RESTORE
    
     VERIFYONLY 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
   
   

?

?

4、从全备份中恢复

不管是在简单恢复模式或者完全恢复模式下和大容量日志模式下，对全备份恢复都是差不多的。最大的差别在于完全恢复模式与大容量日志恢复模式，除了需要恢复全备份外，还需要指定WITH NORECOVERY,以便后续的差异备份和日志备份的恢复。

当然，在简单恢复模式下，也是有差异备份的，这种情况下，同样需要指定WITH NORECOVERY。

需要注意的是，在做恢复之前，应该养成对当前日志进行备份的习惯，否则容易造成数据丢失。SQL Server虽然提供REPLACE选项以便强制恢复，但这样子末尾日志就丢掉了。

通常，如果未对当前日志进行备份，那么会收到如下错误

上述错误，提到了使用WITH REPLACE 或者WITH STOPAT命令来完成恢复，但应尽量避免使用这两个命令。

如果要将数据库恢复到不同的磁盘上，可以通过GUI页面指定，也可以通过T-SQL语句来实现。

T-SQL实现的方式如下：

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
     
    
    WITH
    
     MOVE 
    
    '
    
    Family
    
    '
    
    TO
    
     
    
    '
    
    D:Family.mdf
    
    '
    
    ,MOVE 
    
    '
    
    Family_Log
    
    '
    
     
    
    TO
    
     
    
    '
    
    D:Family.ldf
    
    '
    
     
    
    WITH
    
     RECOVERY
   
   

?

?

5、恢复到指定的时间点

要恢复到指定的时间点有三种选择，一种是通过明确指定时间，一种通过指定LSN号，另外一种则是通过创建和指定log marks。
A. TIME

通常情况下，恢复都会有要求恢复到指定时间点的要求，可通过GUI界面来实现，也可以通过T-SQL 来实现

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    LOG
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.trn
    
    '
    
     
    
    WITH
    
     RECOVERY,STOPAT 
    
    '
    
    jan 8,2009 3:10pm
    
    '
    
     
   
   

B. LSN

如果知道确切的LSN号，也可以通过LSN号来恢复指定的LSN。获取LSN相关信息，可以通过RESOTRE HEADERONLY。这种方式只能通过T-SQL来实现。

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
     
    
    WITH
    
     NORECOVRY 
    
    RESTORE
    
     
    
    LOG
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.trn
    
    '
    
     
    
    WITH
    
     RECOVRY,STOPATMARK LSN:
    
    2433
    
    :
    
    5422
   
   

C. Log Marks

?也可以通过创建Log Mark，可以恢复至指定的Log Marks。例如，创建了一个logmarkexample，则在恢复时，恢复到logmarkexample。

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
     
    
    WITH
    
     NORECOVRY 
    
    RESTORE
    
     
    
    LOG
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.trn
    
    '
    
     
    
    WITH
    
     RECOVRY,STOPATMARK 
    
    '
    
    logmarkexample
    
    '
   
   

6、对镜像备份或条带备份的恢复

对镜像备份而言，每一份备份都是一样的，因此恢复任何一份备份都可以完成恢复。

对条带备份而言，则需要同时指定所有的条带备份，这种备份可以比单独一个备份来得快。

条带备份例子如下：

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
    ,
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
     
    
    WITH
    
     NORECOVRY
   
   

7、恢复数据页

在SQL Server2005以后，SQL Server提供了对数据页恢复的功能。对数据页恢复可以在联机或者脱机状态下进行

但只能对实际用户数据页进行恢复，而其他的页则无法通过备份来恢复。如Global Allocation Map(GAM),Secondary Global Allocation Map(SGAM),Page Free Space(PFS)等。

恢复数据页相当于进行完全恢复，不同的是需要指定具体的页面。

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family PAGE 
    
    '
    
    20:1570,20:1571,20:1572
    
    '
    
     
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    D:family_20100108.bak
    
    '
    
     
    
    WITH
    
     NORECOVRY
   
   

数据页可以通过MSDB..SUSPECT_PAGE或者DBCC CHECKDB来查找。

8、对系统数据库的恢复

系统数据库存储着一个SQL Server实例上相关数据库的信息，如果丢失，将会带来更大的损失。
A. MASTER

Master数据库的恢复与其他数据库的恢复是不同的。要恢复MASTER数据库，需要从将SQL Server切换至单用户模式，如果无法切换，则停止SQL Server服务，然后用sqlserver –m命令行启动。

当然，也可以用net start “服务器名” 来启动SQL Server服务。启动后，再用SQLCMD命令进行还原

B. MSDB

在MSDB里面存储得比较多的是SQL Agent里的内容，如作业，调度，操作员，警告等信息；同时还存放SQL Server Integration Service（SSIS）等信息。

其恢复过程与普通数据库恢复过程是一样的。并且由于是在简单模式下，因而其恢复过程更加简单。

C. MODEL

Model数据库用来存放创建数据库时需要的信息，如果有使用MODEL数据库的话，也需要对其进行备份和恢复。

备份与恢复的过程与普通数据库一致。
D. Tempdb

Tempdb是不需要备份和恢复的，在每次的启动过程中，SQL Server会自动清除tempdb，并重新启动tempdb。在tempdb上需要注意的是其空间规划，因为某些情况下tempdb会变得非常大，耗尽空间，最终导致SQL Server关掉。

若要修改tempdb的存储路径，请使用如下语句：

   
   
    
    use
    
     master 
    
    go
    
     
    
    Alter
    
     
    
    database
    
     tempdb modify 
    
    file
    
     (name 
    
    =
    
     tempdev,filename 
    
    =
    
     
    
    '
    
    E:Sqldatatempdb.mdf
    
    '
    
    ) 
    
    go
    
     
    
    Alter
    
     
    
    database
    
     tempdb modify 
    
    file
    
     (name 
    
    =
    
     templog,filename 
    
    =
    
     
    
    '
    
    E:Sqldatatemplog.ldf
    
    '
    
    ) 
    
    Go
   
   

E. Resource

Resource是SQL Server2005以后新引进的一个数据库，将以前存放于master等其他系统数据库的部分信息存放于Resource数据库里。

对Resource不能通过T-SQL或者GUI备份，因为看不到它，要对其进行备份，只能通过手工直接拷贝其物理文件。

6、数据文件备份的恢复

在第三部份里面提到了文件的备份，这种单个或多个文件的备份，其恢复方式与数据库恢复类似。

但要养成一个良好的习惯，在恢复前，备份当前的日志文件。

   
   
    
    BACKUP
    
     
    
    LOG
    
     Family 
    
    TO
    
     
    
    DISK
    
    =
    
    '
    
    E:Familylog.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY
   
   

接着对需要还原的文件进行还原，如 ?

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FILE
    
    =
    
    '
    
    D:DATAFamily.mdf
    
    '
    
     
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familyprimary.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    LOG
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familylog.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    WITH
    
     RECOVERY 
   
   

如果在数据文件上还有差异备份，日志恢复前进行差异备份恢复，如：

   
   
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FILE
    
    =
    
    '
    
    D:DATAFamily.mdf
    
    '
    
     
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familyprimary.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    FILE
    
    =
    
    '
    
    D:DATAFamily.mdf
    
    '
    
     
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familyprimay.dif
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    LOG
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familylog.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    WITH
    
     RECOVERY
   
   

7、文件组的恢复

与数据文件恢复原理是一致的，只不过是将具体文件文件组。

   
   
    
    BACKUP
    
     
    
    LOG
    
     Family 
    
    TO
    
     
    
    DISK
    
    =
    
    '
    
    E:Familylog.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    DATABASE
    
     Family FILEGROUP
    
    =
    
    '
    
    PRIMARY
    
    '
    
     
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familyprimary.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    LOG
    
     Family 
    
    FROM
    
     
    
    DISK
    
    =
    
    '
    
    E:Familylog.bak
    
    '
    
     
    
    WITH
    
     NORECOVERY 
    
    RESTORE
    
     
    
    DATABASE
    
     Family 
    
    WITH
    
     RECOVERY
   
   

五、小结 对SQL Server来讲，从2005开始，提供了很多智能化的备份方式，如可以通过制定维护计划来进行备份，并自动产生备份作业，通过结合SQL Mail就能够方便DBA建立一个良好的备份计划。但备份是与恢复结合在一起的，备份的目的是为了减少数据丢失，而要求数据零丢失，又需要间隔更短的备份周期，进而影响性能。因此，一个良好的备份计划应该要是多种因素的折中。 一个良好的备份/恢复计划，应该要做好如下几点： 1、 文档化的数据保护需求； 2、 文档化的日/周/月的备份计划； 3、 文档化的恢复过程； 4、 文档化的测试及验证结果。 可见，关键在于文档化，养成良好的文档功能是很必要的

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!