sql数据库备份和还原

1、SQL 备份与还原经典文档SQL 备份1、SQL 数据库恢复模型 1)完全恢复模型(1)备份时要备份数据库的数据文件和日志文件(2)还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。(3)能还原全部数据，并可以将数据库恢复到任意指定的时刻。(4)为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。(2)大容量日志记录恢复模型(1)备份时要备份数据库的数据文件和日志文件(2)还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。(3)日志中不记录*作细节(如 selectinto、createindex 等)，而只记录*作

2、的最终结果，因此占用日志空间小。(4)只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。(3)简单恢复模型(1)备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。(2)只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。(3)不适于生产系统和大规模*作环境下选用。alterdatabased1setrecoverysimple-设置数据库恢复模型alterdatabased1setrecoverybulk_loggedalterdatabased1setrecoveryfull2、备份设备 1)物理设备 disk：支持本地磁盘或者网络备份tape

3、：支持磁带机备份namepipe：支持第三方备份软件 2)逻辑设备永久备份文件：可以重复使用，应该在备份前创建。临时备份文件：用于一次性备份，在备份时创建。execsp_addumpdevicedisk,bak2,e:back_devicebak2.bak-创建永久磁盘备份设备execsp_addumpdevicedisk,bak3,e:back_devicebak3.bakexecsp_addumpdevicedisk,bak4,sv2backupbak4.bak-创建网络永久磁盘备份设备execsp_addumpdevicedisk,bak5,sv2backupbak5.bakexecsp

4、_dropdevicebak5-删除备份设备backupdatabased3tobak3-将数据库备份到备份设备backupdatabased4tobak4restoreheaderonlyfrombak2-查看备份设备中的内容backupdatabased3todisk=e:back_filed3.bak-将数据库备份到临时备份文件backupdatabased4todisk=e:back_filed4.bakrestoredatabased3frombak3-从备份设备还原数据库restoredatabased4fromdisk=e:back_filed4.bak-从备份文件还原数据库 3

5、、 使用多个备份文件存储备份 1）SQL 可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。3）媒体集中的文件必须同时使用，而不能单独使用。4）可以通过 format 命令将媒体集重新划分，但原备份集中的数据不能再使用。backupdatabased4tobak4,bak5,bak6withmedianame=bak456,format-备份 D4 并形成 MediaSetbackupdatabased3tobak4-失败，因 Mediaset 中文件必须同时使用backupdatabased3to

6、bak4,bak5,bak6-成功，将 D3 也备份到 MediaSet 中restoreheaderonlyfrombak4,bak5,bak6-查看 MediaSet 中的备份内容backupdatabased4tobak4withmedianame=bak4,format-重新戈 U 分 MediaSetbackupdatabased3tobak5,bak6withmedianame=bak56,formatbackupdatabased1tobak1withinit-withinit 重写备份设备中内容backupdatabased2tobak1withnoinit-withnoini

7、t 将内容追加到备份设备中 restoreheaderonlyfrombak14、备份的方法 1)完全备份(1)是备份的基准。在做备份时第一次备份都建议使用完全备份。(2)完全备份会备份数据库的所有数据文件、数据对象和数据。(3)会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。backupdatabaseditobaklwithinit-完全备份 backupdatabaseditobakiwithnoinit2)差异备份(i)基于完全备份。(2)备份自最近一次完全备份以来的所有数据库改变。(3)恢复时，只应用最近一次完全备份和最新的差异备份。backupdatabase

8、d2tobak2withinit,name=d2_full-差异备份，第一次备份时应做完全备份createtablebi(ciintnotnull,c2char(10)notnull)backupdatabased2tobak2withdifferential,name=d2_diffiinsertbivalues(i,a)backupdatabased2tobak2withdifferential,name=d2_diff2insertbivalues(2,b)backupdatabased2tobak2withdifferential,name=d2_diff3insertbivalues

9、(3,c)backupdatabased2tobak2withdifferential,name=d2_diff4restoreheaderonlyfrombak23)事务日志备份(i)基于完全备份。(2)为递增备份，即备份从上一次备份以来到备份时所写的事务日志。(3)允许恢复到故障时刻或者一个强制时间点。(4)恢复时，需要应用完全备份和完全备份后的每次日志备份。backupdatabased3tobak3withinit,name=d3_full-日志备份，第一次备份时应做完全备份createtablebi(ciintnotnull,c2char(i0)notnull)backuplogd3

10、tobak3withinsertbivalues(i,a)backuplogd3tobak3withinsertbivalues(2,b)backuplogd3tobak3withinsertbivalues(3,c)backuplogd3tobak3withrestoreheaderonlyfrombak3createtableb1(c1intnotnull,c2char(10)notnull)-Full+Log+Diffbackuplogd4tobak4withinsertb1values(1,a)backuplogd4tobak4withinsertb1values(2,b)backup

11、databased4tobak4withdifferential,name=d4_diff1insertb1values(3,c)backuplogd4tobak4withinsertb1values(4,d)backuplogd4tobak4withinsertb1values(5,d)backupdatabased4tobak4withdifferential,name=d4_diff2restoreheaderonlyfrombak4日志清除1)如果日志空间被填满，数据库将不能记录修改。2)数据库在做完全备份时日志被截断。3)如果将Translogoncheckpoint选项设为 TRU

12、E,则结果为不保存日志，即没有日志记录,不建议使用。4) withtruncate_only 和 withno_log 设置日志满时清除日志5) withno_truncate 则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库疝问题后在恢复前使用。可以将数据还原到出故障的那一时刻。execsp_dboptiond3execsp_dboptionsp_dboptiond3,trunc.logonchkpt.,true-设置自动清除数据库日志sp_dboptiond3,trunc.logonchkpt.,false-将自动清除数据库日志的选项去除backuplogd4wit

13、htruncate_only-设置 D4 日志满时清除日志，并做清除记录backuplogd4withno_log-设置 D4 日志满时清除日志，但不做清除记录backuplogd4tobak4withno_truncate-在 D4 数据库损坏时马上备份当前数据库日志(DEMO)使用 no_truncate完全+修改 1+差异+修改 2+差异+修改 3+停止 SQL,删除数据库数据文件+重启 SQLbackuplogno_truncate再还原，可还原到修改 34)文件/文件组备份(1)用于超大型数据库。(2)只备份选定的文件或者文件组。(3)必须同时作日志备份。(4)还原时用文件/文件组备

14、份和日志备份进行还原。(5)备份量少，恢复速度快。createdatabased5onprimary(name=d5_data1,filename=e:datad5d5_data1.mdf,size=2MB),filegroupFG2 一创建数据库时创建 filegroupFG2(name=d5_data2,filename=e:datad5d5_data2.ndf,一并将文件 d5_data2 放至 UFG2 中size=2Mb)logon(name=d5_log1,filename=e:datad5d5_log1.ldf,size=2Mb)used5goalterdatabased5add

15、file(name=d5_data3,filename=e:datad5d5_data5.ndf,size=2MB)tofilegroupFG2将 d5_data3 加到文件组 FG2 中alterdatabased5addfilegroupFG3 一增加文件组 FG3alterdatabased5-4 各 d5_data4 力口至 U 文件组 FG2 中addfile(name=d5_data4,filename=e:datad5d5_data4.ndf,size=2MB)tofilegroupFG3sp_helpdbd5createtablet1(c1intnotnull,c2char(1

16、0)notnull)onprimary-将不同表放到不同 filegroup 中createtablet2(c1intnotnull,c2char(10)notnull)onFG2createtablet3(c1intnotnull,c2char(10)notnull)onFG3backupdatabased5tobak5withinit,name=d5_full-filegroup 备份backupdatabased5filegroup=primarytobak5withbackuplogd5tobak5withbackupdatabased5filegroup=FG2tobak5withb

17、ackuplogd5tobak5withbackupdatabased5filegroup=FG3tobak5withbackuplogd5tobak5withbackupdatabased5tobak6withinit,name=d5_full-file 备份backupdatabased5file=d5_data1tobak6withbackuplogd5tobak6withbackupdatabased5file=d5_data2tobak6withbackuplogd5tobak6withbackupdatabased5file=d5_data3tobak6withbackuplogd

18、5tobak6withbackupdatabased5file=d5_data4tobak6withbackuplogd5tobak6withrestoreheaderonlyfrombak6SQL 还原 1、验证备份 restoreheaderonlyfrombak3restorefilelistonlyfrombak3withfile=1restorelabelonlyfrombak3restoreverifyonlyfrombak32、从备份中还原 restoreheaderonlyfrombak1restoredatabased1frombak1withfile=2 一从完全备份中恢复

19、restoreheaderonlyfrombak2 一从差异备份中恢复restoredatabased2frombak2withfile=1,norecoveryrestoredatabased2frombak2withfile=5,recoveryrestoreheaderonlyfrombak3 一从日志备份中恢复restoredatabased3frombak3withfile=1,norecoveryrestorelogd3frombak3withfile=2,norecoveryrestorelogd3frombak3withfile=3,norecoveryrestorelogd3

20、frombak3withfile=4,norecoveryrestorelogd3frombak3withfile=5,recoveryrestoredatabased3frombak3withfile=1,norecoveryrestorelogd3frombak3withfile=2,norecoveryrestorelogd3frombak3withfile=3,norecoveryrestorelogd3frombak3withfile=4,recovery,stopat=2003-08-1511:29:00.000,restoredatabased5filegroup=FG2from

21、bak5withfile=4,norecovery-还原文件组备份restorelogd5frombak5withfile=5,norecoveryrestorelogd5frombak5withfile=7,recoveryrestoreheaderonlyfrombak6-还原文件备份restoredatabased5file=d5_data3frombak6withfile=6,norecoveryrestorelogd5frombak6withfile=7,norecoveryrestorelogd5frombak6withfile=9,recoveryrestoredatabased

22、5frombak6withreplace-删除现有数据库，从备份中重建数据库createdatabased6-moveto 将数据库文件移动到新位置onprimary(name=d6_data,filename=E:ProgramFilesMicrosoftSQLServerMSSQLdatad6_Data.MDF,size=2MB)logon(name=d6_log,filename=E:ProgramFilesMicrosoftSQLServerMSSQLdatad6_log.ldf,size=2MB)go-恢复到指定时间backupdatabased6tobak6withinitdrop

23、databased6restoredatabased6frombak6withmoved6_datatoe:datad6d6_data.mdf,moved6_logtoe:datad6d6_log.ldfsp_helpdbd63、分离与重连接数据库 sp_detach_dbd6sp_attach_dbd6,e:datad6d6_data.mdf,e:datad6d6_log.ldfsp_detach_dbd6gocreatedatabased6onprimary(filename=e:datad6d6_data.mdf)forattachgo4、恢复损坏的系统数据库1)先备份 MASTER、M

24、SDB2)停止 SQL 服务，将 MASTER 数据库文件删除或者重命名。这样，SQL 服务将不能启动。3)系统数据库的还原(1)如果 SQL 服务还能启动，则从备份中恢复系统数据库。(2)如果 SQL 服务不能启动，则需要重建系统数据库。使用 SQL 文件夹 TOOLSBINN 目录下的 Rebuildm.exe 重建 master 数据库。(3)创建备份设备，指向以前的备份设备。(4)以单用户模式启动 SQLcdprogramefilesmicrosoftsqlservermssqlbinnsqlservr.exe-c-m(5)进查询分析器，从备份中恢复 master 数据库。restor

25、edatabasemasterfrommasterbakrestoredatabasemsdbfromdisk=e:bakmsdb.bakMASTER 还原后，SQL 中用户数据库的信息也会恢复。(6)如果 MASTER 没有备份，则需要用 sp_attach_db 命令将用户数据库附加到新的 MASTER数据库中。全备份+差异备份：以一周为周期，星期日进行全备份，星期一到星期六每天进行差异备份。全备份十日志备份：以一周为周期，星期日进行全备份，星期一到星期六每天进行日志备份。文件组备份+日志备份：备份周期取决于数据库的大小和能力，每周期分别进行一部分数据文件备份，每天进行日志备份。手动创建备份：使用 T_SQL 语句创建备份设备。在 SQLServer 中使用 sp_addumpdevice 语句创建备份设备，其语法形式如下：sp_addumpdevice 磁盘类型，备份设备的逻辑名称，备份设备的物理名称例：创建备份设备 zwbackusemasterExecsp_addumpdevicedisk；zwback:cback.bak-创建备份设备execsp_dropdevicezwback-删除备份设备T-SQL 创建完全备份语法：