SQLServer2008备份和恢复及作业

1、目标：理解SQLServer2008R2的备份方式(完整，差异，日志备份)和恢复模式；能够进行完整备份，差异备份操作,事务日志备份，并进行恢复操作;使用维护计划实现日常的数据库备份操作;一、SQLServer2008R2的备份恢复1、恢复模式类型所有的数据库都可以设置为三个不同的恢复模式：简单(simple),完全(full),大容量日志(Bulk-Logged).A完全恢复模式完全恢复模式是默认的恢复模式。在完全恢复模式下，需要手工的对事务日志进行管理。使用完全恢复模式的优点是可以恢复到数据库失败或者指定的时间点上。缺点则是，如果没有进行管理的话，事务日志将会快速增长，消耗磁盘空间。要清除事

2、务日志，只能通过备份事务日志，或者切换至简单模式。B简单恢复模式与完全恢复模式不同的是，在简单恢复模式下，在检查点发生时(checkpoint),当前已被提交的事务日志将会被清除。因此，在简单恢复模式下，容易造成数据丢失，因为无法将数据库恢复到失败的那一刻。需要注意的是，虽然在简单恢复模式下，系统会自动定期清除日志，但这并不意味着事务日志文件不会增长。例如，如果执行一个批量插入操作时，SQLSERVER会将该相关操作当成一个事务，期间产生的日志量在极端情况下，还是非常可观的。C大容量日志恢复模式大容量日志恢复模式与完全恢复模式非常相似，但与完全恢复模式不同的是，批量操作将会尽量被最少记录。批量

3、操作有以下几种类型：批量导入数据，例如使用BCP(BulkCopyImport),BULKINSERT命令,或者是在BULK使用OPENROWSET命令；大对象操作(LOB),例如在TEXT,NTEXT,IMAGE列上使用WRITETEXT或者UPDATETEXT；SELECTINTO字句；CREATEINDEX,ALTERINDEX,ALTERINDEXREBUILD,DBCCREINDEX在完全恢复模式下，上述操作产生的日志将会是非常大的。而使用大容量日志恢复模式将会阻止不需要或者非预期的日志增长。在批量操作发生时，SQLSERVER仅仅记录了相关数据页(datapage)的ID,在SQL

4、SERVER中，SQLSERVERpages都有内部ID,如5：547。用这种方式，能够将大量的pageID记录在小的日志文件里。使用大容量日志恢复模式，将会使数据仓库或者有大批量操作的数据库减少很大的空间。但使用大容量恢复模式时，会使得恢复变得比较困难，一般来说，只能恢复到最后的事务日志备份点上，但如果所有的事务日志都被备份后，还是可以恢复成功的。2、改变恢复模式改变数据库的恢复模式，可以通过以下语句来实现：ALTERDATABASEdatabase_nameSETRECOVEYBULK_LOGGED改变恢复模式并不需要重启数据库实例。二、数据库备份介绍1、备份位置在SQLServer上，有

5、多种备份位置可以选择，如本地磁盘，网络磁盘，远程地址，磁带等。各种备份位置均有自己的优点和缺点。2、逻辑备份设备在SQLServer上，可以通过创建逻辑备份设备来完成备份。使用逻辑备份设备的好处是，当变更备份地址时，不需要更改备份脚本，只需要更改逻辑备份设备的定义即可。创建逻辑备份设备的脚本如下：SQLcodeEXECsp_adddumpdevicedevtype=disk,logicalname=MYBackup,physicalname=D:backupmydb.bak删除备份设备的脚本：SQLcodeSp_dropdevicelogicalname=MYBackup上述脚本只是删除逻辑备

6、份设备的定义，下述脚本将同时删除备份文件：SQLcodeSp_dropdevicelogicalname=MYBackup,devfile=DELFILE使用逻辑备份设备的方法如下：SQLcodeBackupdatabasemydbtoMYBackup当然，还可在逻辑备份设备上指定过期时间等备份属性，如：SQLcodeBackupdatabasemydbtoMYBackupWITHEXPIREDATE=13/01/2010或：SQLcodeBACKUPDATABASEmydbtoMYBackupWITHRETAINDAYS=73、备份集与存储集每一份备份包含于一个备份集，而一个备份集包含于一个

7、存储集。通过系统GUI进行备份时,SQLServer会自动指定备份集和存储集，目的则是为了简化管理。用T-SQL显示指定则用如下语法：SQLcodeBACKUPDATABASEmydbtoMYBackupWITHRETAINDAYS=7,NAME=FULL,MEDIANAME=ALLBackupsNAMEs是指备份集名称，MEDIANAME是指存储集名称。4、全备份不管恢复模式是哪一个，所有的备份都必须要有一个全备份，特别是日志备份和差异备份，如果没有全备份的话，将无法进行恢复。简单的全备份脚本如下所示，也可以通过维护计划来指定全备份SQLcodeBACKUPDATABASEmydbtoDIS

8、K=D:Backupmydb.bak但需要注意的是，上述命令是将数据库备份附加到当前的存在的文件上，如果不存在则创建它，并不会覆盖原有文件。要覆盖同名的备份文件，需要指定INIT参数。SQLcodeBACKUPDATABASEmydbtoDISK=D:Backupmydb.bakWITHINIT5、日志备份在完全恢复模式或者大容量日志恢复模式下，日志备份不仅仅是恢复的需要，同时也是手工管理事务日志文件的一种方式。如果从不进行备份的话，在完全恢复模式或者大容量恢复模式下，事务日志将会持续增长，直至消耗完所在磁盘。日志备份的脚本如下：SQLcodeBACKUPLOGmydb_logTODISK=D

9、:backupmydb.trn需要养成使用.trn为日志备份的扩展名的习惯。每个在数据库上的动作都会被安排一个LogSequenceNumber(LSN)。如果需要还原到指定的时间点，需要有持续的LSN记录。也就是说，在完全恢复模式或者大容量日志模式下，一个不被打断的事务日志备份链是恢复数据库的基本要求。6、差异备份使用日志备份来恢复时，无疑是一个很慢的过程，特别是上一个全备份的历史比较悠久时。使用差异备份，便能缩短恢复时间。事实上，差异备份只是BACKUPDATABASE的一个选项，如下：SQLcodeBACKUPDATABASEmydbTODISK=D:backupmydb.difWITH

10、DIFFERENTIAL,INIT进行数据库恢复时，先恢复数据库全备份，再恢复数据库差异备份，最后才恢复日志备份。差异备份是与上一次全备份紧密相连的，不管期间有多少次日志备份和差异备份，差异备份还是会从上一次全备开始备份。因此，经常会遇到这样的一种情况，在生产库上需要临时使用数据库时，便用BACKUPDATABASETODISK=.进行了一个备份，下一次的差异备份便会以这回的全备为准，如果过后把这个临时全备删除掉后，后面的差异备份就没用了。差异备份并不意味着磁盘空间肯定会少，这取决于实际情况。当期间大量操作发生时，差异备份还是会变得很大。7、错误检测在备份过程中，备份进程会同时验证数据，或者校

11、验不完整页(tornpage)，或者验证校验和(checksum)。要使用该功能，需要激活该选项。不完整页检测(Torndection)仅仅检查每一个页看是否已经写完成。如果发现一个页只有部分被写入，那么就将其标记为torn。校验和验证(checksumvalidation)是一种新的页验证机制。它会为每个页添加一个值来表明该页实际的大小。虽然看起来是个代价很高影响性能的操作，但事实上，它的效率非常高，与torn差不多。备份进程在备份数据库时，会通过比较在数据库里的和随着备份页写入硬盘时这两个之间的值来进行验证。但是，这个验证并不是自动完成的，需要显示指定，在GUI页面上是个选项。如果通过T-

12、SQL来备份的话，语句如下：SQLcodeBACKUPDATABASEmydbTODISK=D:datamydb.bakWITHCHECKSUM如果备份过程中，发现了错误，SQLServer会错误信息写入MSDB上的SUSPECT_PAGE表里面。同时，在默认情况下，备份行为会停止的(STOP_ON_ERROR),以便管理员排查错误。但备份过程中的校验和验证还有另外一个选项(CONTINUE_ON_ERROR)，也就是说，如果发现错误，备份过程并不会中断，而是将错误页信息记录在MSDB.SUSPECT_PAGE上而已。需要注意的是，SUSPECT_PAGE表是有行限制的，最多只能达到1000行

13、，如果达到了的话，备份同样会失败。激活校验和验证的话，很明显会影响备份的性能。但还是很有必要的。8、安全备份完全备份和日志备份语句还支持使用密码属性，如：SQLcodeBACKUPDATABASEmydbTODISK=D:mydb.bakWITHPASSWORD=mydb所指定的密码是很容易破解的。因此，如果确实需要对某些备份数据进行加密的话，可以将备份存放于加密的文件系统或者其它安全的存储设备上。同时，SQLServer还提供了对真实列进行加密的功能。该加密功能是工业标准。9、条带备份有些情况下，单独一个硬盘无法存储一个完整的数据库备份时，可以将数据库备份分成多个部分存储在不同的磁盘上，这种

14、备份方式成为条带备份。使用条带备份的优点很明确，就是能很好的利用空间，但如果某部分备份丢失或者损坏，那整个备份将无效。其语句如下：SQLcodeBACKUPDATABASEmydbTODISK=D:mydb.bak,DISK=E:mydb.bakWITHINIT,CHECKSUM,CONTINUE_ON_ERROR上述D盘和E盘上的备份是不可分割的。10、镜像备份与条带备份在多个磁盘上保留同一份备份不同的是，镜像备份是在不同磁盘上保留多份备份。其语句如下：SQLcodeBACKUPDATABASEmydbTODISK=D:mydb.bakMIRRORTODISK=E:mydb.bakWITHI

15、NIT,CHECKSUM,CONTINUE_ON_ERROR在实际情况下，对日志备份采取镜像备份方式会比较合适。11、COPY-ONLY备份在差异备份里曾提到过，差异备份是建立在上一个全备份的基础上的。因此如果在一个事先安排好的备份计划里，如果在全备份和差异备份之间再进行了一次全备份后，其差异备份会被打断，如果把临时全备份删除掉后，就产生了数据丢失。在SQLSERVER2005以后，SQLSERVER提供了一个选项copy-only.使用copy-only选项进行的全备份便不会打算原先的备份计划，语句如下：SQLcodeBACKUPDATABASEmydbTODISK=D:mydb.bakWI

16、THINIT,CHECKSUM,COPY_ONLY三、文件和文件组备份1、备份数据文件备份数据文件同样可以通过BACKUPDATABASE语句来实现。如下：SQLcodeBACKUPDATABASEmydbFILE=D:Datamydb.ndfTODISK=E:Backupmydbdata.bak上述语句相当于数据文件级别的全备份，与数据库级别的备份类似，文件级别上的备份也有差异备份，当然前提是要有相对应的文件全备份。差异备份的语句如下：SQLcodeBACKUPDATABASEmydbFILE=D:Datamydb.ndfWITHDIFFERENTIALTODISK=E:Backupmydb

17、data_dif.bak5、备份文件组与单独备份文件类似，也可以对文件组进行类似的备份操作。备份文件组的方式也有两种，一种是通过GUI界面指定，一种则是通过T-SQL。T-SQL的语句如下：SQLcodeBACKUPDATABASEmydbFILEGROUP=PRIMARYTODISK=E:Backupmydbpri.bak6、不完全备份(partialbackup)在文件组备份上，不完全备份其实相当于完全备份，可以通过指定关键字READ_WRITE_FILEGROUPS来实现不完全备份。语句如下：SQLcodeBACKUPDATABASEmydbREAD_WRITE_FILEGROUPSTO

18、DISK=D:mydb.bak那不完全备份到底是什么意思呢？什么时候需要不完全备份？如果对一个文件组设置了只读，而这只读的文件组又需要进行一次备份，这时，可以不用BACKUPDATABASE语句进行备份，只需要挑个时间停止实例，然后执行不完全备份。四、数据恢复1、Restorevs.RecoveryRestore和Recovery是两个不同的概念，但在数据恢复过程中又是紧密联系的。Restore相当于从备份集中重建整个或者部分数据库，Restore是无法改变数据库状态的，如脱机和联机等。一旦对数据库进行了Recovery，则将无法再进行Restore操作。数据库恢复的语句如下：SQLcodeR

19、ESTOREDATABASEmydbFROMmydbdeviceWITHRECOVERY显示指定RECOVERY或者NORECOVERY是个良好的习惯。2、备份文件里面的信息在primary文件里，存储着与数据库结构有关的一些信息，如文件位置等。因此备份后，这些信息同样保留在备份文件里面。如果要恢复到不同的磁盘上或服务器后，需要进行额外的更改。在SQLServer里，提供了RESTOREHEADERONLY,RESTOREFILELISTONLY,RESTORELABELONLY,RESTOREVERIFYONLY等命令来读取备份文件的信息。也可以通过MSDB里的表来获取备份集的相关信息。AR

20、ESTOREHEADERONLYSQLcodeRESTOREHEADERONLYFROMDISK=D:family_20100108.bak各字段的含义可以查看联机丛书。RESOTREFILELISTONLYSQLcodeRESTOREFILELISTONLYFROMDISK=D:family_20100108.bakRESOTRELABELONLYSQLcodeRESTORELABELONLYFROMDISK=D:family_20100108.bakRESOTOREVERIFYONLYSQLcodeRESTOREVERIFYONLYFROMDISK=D:family_20100108.ba

21、k3、从全备份中恢复需要注意的是，在做恢复之前，应该养成对当前日志进行备份的习惯，否则容易造成数据丢失。SQLServer虽然提供REPLACE选项以便强制恢复，但这样子末尾日志就丢掉了。通常，如果未对当前日志进行备份，那么会收到如下错误上述错误，提到了使用WITHREPLACE或者WITHSTOPAT命令来完成恢复，但应尽量避免使用这两个命令。如果要将数据库恢复到不同的磁盘上，可以通过GUI页面指定，也可以通过T-SQL语句来实现。T-SQL实现的方式如下：SQLcodeRESTOREDATABASEFamilyFROMDISK=D:family_20100108.bakWITHMOVEFa

22、milyTOD:Family.mdf,MOVEFamily_LogTOD:Family.ldfWITHRECOVERY4、恢复到指定的时间点要恢复到指定的时间点有三种选择，一种是通过明确指定时间，一种通过指定LSN号，另外一种则是通过创建和指定logmarks。TIME通常情况下，恢复都会有要求恢复到指定时间点的要求，可通过GUI界面来实现，也可以通过T-SQL来实现SQLcodeRESTOREDATABASEFamilyFROMDISK=D:family_20100108.bakWITHNORECOVERYRESTORELOGFamilyFROMDISK=D:family_20100108.

23、trnWITHRECOVERY,STOPATjan8,20093:10pmLSN如果知道确切的LSN号，也可以通过LSN号来恢复指定的LSN。获取LSN相关信息，可以通过RESOTREHEADERONLY。这种方式只能通过T-SQL来实现。SQLcodeRESTOREDATABASEFamilyFROMDISK=D:family_20100108.bakWITHNORECOVRYRESTORELOGFamilyFROMDISK=D:family_20100108.trnWITHRECOVRY,STOPATMARKLSN:2433:5422LogMarks也可以通过创建LogMark，可以恢复至

24、指定的LogMarks。例如，创建了一个logmarkexample，贝V在恢复时，恢复至Ulogmarkexample。SQLcodeRESTOREDATABASEFamilyFROMDISK=D:family_20100108.bakWITHNORECOVRYRESTORELOGFamily数据页可以通过MSDB.SUSPECT_PAGE或者DBCCCHECKDB来查找。FROMDISK=D:family_20100108.trnWITHRECOVRY,STOPATMARKlogmarkexample5、对镜像备份或条带备份的恢复对镜像备份而言，每一份备份都是一样的，因此恢复任何一份备份都

25、可以完成恢复。对条带备份而言，则需要同时指定所有的条带备份，这种备份可以比单独一个备份来得快。条带备份例子如下：SQLcodeRESTOREDATABASEFamilyFROMDISK=D:family_20100108.bak,DISK=D:family_20100108.bakWITHNORECOVRY6、恢复数据页在SQLServer2005以后，SQLServer提供了对数据页恢复的功能。对数据页恢复可以在联机或者脱机状态下进行但只能对实际用户数据页进行恢复，而其他的页则无法通过备份来恢复。如GlobalAllocationMap(GAM),SecondaryGlobalAllocat

26、ionMap(SGAM),PageFreeSpace(PFS)等。恢复数据页相当于进行完全恢复，不同的是需要指定具体的页面。SQLcodeRESTOREDATABASEFamilyPAGE20:1570,20:1571,20:1572FROMDISK=D:family_20100108.bakWITHNORECOVRY7、对系统数据库的恢复系统数据库存储着一个SQLServer实例上相关数据库的信息，如果丢失，将会带来更大的损失。MASTERMaster数据库的恢复与其他数据库的恢复是不同的。要恢复MASTER数据库，需要从将SQLServer切换至单用户模式，如果无法切换，则停止SQLSer

27、ver服务，然后用sqlserver-m命令行启动。当然，也可以用netstart“服务器名”来启动SQLServer服务。启动后，再用SQLCMD命令进行还原MSDB在MSDB里面存储得比较多的是SQLAgent里的内容，如作业，调度，操作员，警告等信息;同时还存放SQLServerIntegrationService(SSIS)等信息。其恢复过程与普通数据库恢复过程是一样的。并且由于是在简单模式下，因而其恢复过程更加简单。MODELModel数据库用来存放创建数据库时需要的信息，如果有使用MODEL数据库的话，也需要对其进行备份和恢复。备份与恢复的过程与普通数据库一致。TempdbTemp

28、db是不需要备份和恢复的，在每次的启动过程中，SQLServer会自动清除tempdb，并重新启动tempdb。在tempdb上需要注意的是其空间规划，因为某些情况下tempdb会变得非常大，耗尽空间，最终导致SQLServer关掉。若要修改tempdb的存储路径，请使用如下语句:SQLcodeusemastergoAlterdatabasetempdbmodifyfile(name=tempdev,filename=E:Sqldatatempdb.mdf)goAlterdatabasetempdbmodifyfile(name=templog,filename=E:Sqldatatemplo

29、g.ldf)GoResourceResource是SQLServer2005以后新引进的一个数据库，将以前存放于master等其他系统数据库的部分信息存放于Resource数据库里。对Resource不能通过T-SQL或者GUI备份，因为看不到它，要对其进行备份，只能通过手工直接拷贝其物理文件。6、数据文件备份的恢复在第三部份里面提到了文件的备份，这种单个或多个文件的备份，其恢复方式与数据库恢复类似。但要养成一个良好的习惯，在恢复前，备份当前的日志文件。SQLcodeBACKUPLOGFamilyTODISK=E:Familylog.bakWITHNORECOVERY接着对需要还原的文件进行还

30、原，如SQLcodeRESTOREDATABASEFamilyFILE=D:DATAFamily.mdfFROMDISK=E:Familyprimary.bakWITHNORECOVERYRESTORELOGFamilyFROMDISK=E:Familylog.bakWITHNORECOVERYRESTOREDATABASEFamilyWITHRECOVERY如果在数据文件上还有差异备份，日志恢复前进行差异备份恢复，如SQLcodeRESTOREDATABASEFamilyFILE=D:DATAFamily.mdfFROMDISK=E:Familyprimary.bakWITHNORECOVERYRESTOREDATABASEFamilyFILE=D:DATAFamily.mdfFROMDISK=E:Familyprimay.difWIT