SQLserver数据备份与恢复

时间：2022-01-25 14:05:49

 

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

 

四、数据恢复

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选项的选择。

数据库恢复的语句如下：

 

SQL code 
 
  
 
   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。

 




   SQL code 
 
 
 
 
  
 
  
 
   sp_configure  
   ' 
   Show Advanced Options 
   ' 
   , 
   1 
   sp_configure  
   ' 
   recovery interval 
   ' 
   , 
   5 
    
   RECONFIGURE 
     
   WITH 
    OVERRIDEsp_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







   SQL code 
 
 
 
 
  
 
  
 
   RESTORE 
    HEADERONLY 
   FROM 
     
   DISK 
   = 
   ' 
   D:\family_20100108.bak 
   ' 
  
 
 
 







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




   SQL code 
 
 
 
 
  
 
  
 
    
   RESTORE 
    FILELISTONLY 
   FROM 
     
   DISK 
   = 
   ' 
   D:\family_20100108.bak 
   ' 
  
 
 
 





C.RESOTRE LABELONLY

 




   SQL code 
 
 
 
 
  
 
  
 
     
   RESTORE 
    LABELONLY 
   FROM 
     
   DISK 
   = 
   ' 
   D:\family_20100108.bak 
   ' 
  
 
 
 





 

D.RESOTORE VERIFYONLY

  
 




   SQL code 
 
 
 
 
  
 
  
 
     
   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实现的方式如下：







   SQL code 
 
 
 
 
  
 
  
 
   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 来实现

 




   SQL code 
 
 
 
 
  
 
  
 
   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来实现。

 




   SQL code 
 
 
 
 
  
 
  
 
   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。

 




   SQL code 
 
 
 
 
  
 
  
 
   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、对镜像备份或条带备份的恢复

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

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

条带备份例子如下：

 




   SQL code 
 
 
 
 
  
 
  
 
   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)等。

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







   SQL code 
 
 
 
 
  
 
  
 
   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的存储路径，请使用如下语句：

 




   SQL code 
 
 
 
 
  
 
  
 
   use 
    master 
   go 
    
   Alter 
     
   database 
    tempdb modify  
   file 
    (name  
   = 
    tempdev, filename  
   = 
     
   ' 
   E:\Sqldata\tempdb.mdf 
   ' 
   ) 
   go 
    
   Alter 
     
   database 
    tempdb modify  
   file 
    (name  
   = 
    templog, filename  
   = 
     
   ' 
   E:\Sqldata\templog.ldf 
   ' 
   ) 
   Go 
  
 
 
 




E. Resource

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

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

6、数据文件备份的恢复

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

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







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





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

 




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





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

 




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





7、文件组的恢复

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







   SQL code 
 
 
 
 
  
 
  
 
   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、 文档化的测试及验证结果。

可见，关键在于文档化，养成良好的文档功能是很必要的
标签：
相关文章