sql数据库备份和还原

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

2、*作的最终结果，因 此占用日志空间小。（4 ）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数 据丢失。（3）简单恢复模型（1 ）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。（3）不适于生产系统和大规模 *作环境下选用。alter database d1 set recovery simple-设置数据库恢复模型alter database d1 set recovery bulk_loggedalter database d1 set recovery full 2、备份设备 1）物理设

3、备 disk:支持本地磁盘或者网络备份 tape：支持磁带机备份name pipe：支持第三方备份软件 2）逻辑设备永久备份文件：可以重复使用，应该在备份前创建。 临时备份文件：用于一次性备份，在备份时创建。exec sp_addumpdevice 'disk','bak2','e:back_devicebak2.bak'-创建永久磁盘备份设备exec sp_addumpdevice 'disk','bak3','e:back_devicebak3.bak' exec sp_addumpdevice

4、 'disk','bak4','sv2backupbak4.bak'-创建网络永久磁盘备份设备exec sp_addumpdevice 'disk','bak5','sv2backupbak5.bak'exec sp_dropdevice 'bak5'-删除备份设备backup database d3 to bak3-将数据库备份到备份设备backup database d4 to bak4restore headero nly from bak2-查看备份设备中的内容backup

5、database d3 to disk='e:back_filed3.bak'-将数据库备份到临时备份文件backup database d4 to disk='e:back_filed4.bak'restore database d3 from bak3-从备份设备还原数据库restore database d4 from disk='e:back_filed4.bak'-从备份文件还原数据库3、使用多个备份文件存储备份1）SQL可同时向多个备份文件进行写 *作。如果把这些文件放到多个磁带机或磁盘中，则 可提高备份速度。2）这多个备份文件必须用

6、同业型的媒体，并放到一个媒体集中。3）媒体集中的文件必须同时使用，而不能单独使用。4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。backup database d4 to bak4,bak5,bak6 with medianame='bak456',format -备份 D4 并形成 Media Setbackup database d3 to bak4 -失败，因 Media set中文件必须同时使用backup database d3 to bak4,bak5,bak6 -成功，将 D3 也备份到 Media Set 中restore head

7、eronly from bak4,bak5,bak6-查看 Media Set 中的备份内容backup database d4 to bak4 with medianame='bak4',format-重新划分 Media Setbackup database d3 to bak5,bak6 with media name='bak56',formatbackup database d1 to bak1 with in it-with in it 重写备份设备中内容backup database d2 to bak1 with n oi nit -with n

8、 oi nit 将内容追加到备份设备中 restore headero nly from bak14、备份的方法1）完全备份（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。（2）完全备份会备份数据库的所有数据文件、数据对象和数据。（3 ）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。backup database di to bakl with in it-完全备份backup database di to baki with noin it2）差异备份（1 ）基于完全备份。（2 ）备份自最近一次完全备份以来的所有数据库改变。（3）恢复时，只应用最近一次完全

9、备份和最新的差异备份。backup database d2 to bak2 with in it, name='d2_full'-差异备份，第一次备份时应做完全备份 create table b1(c1 int not null,c2 char(10) n ot null)backup database d2 to bak2 with differe ntial, name='d2_diff1'in sert b1 values(1,'a')backup database d2 to bak2 with differe ntial, name=&

10、#39;d2_diff2'in sert b1 values(2,'b')backup database d2 to bak2 with differe ntial, name='d2_diff3'in sert b1 values(3,'c')backup database d2 to bak2 with differe ntial, name='d2_diff4'restore headero nly from bak23）事务日志备份（1 ）基于完全备份。（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志

11、。（3 ）允许恢复到故障时刻或者一个强制时间点。（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。backup database d3 to bak3 with in it, name='d3_full'-日志备份，第一次备份时应做完全备份 create table b1(c1 int not null,c2 char(10) n ot null)backup log d3 to bak3 within sert b1 values(1,'a')backup log d3 to bak3 within sert b1 values(2,'b

12、9;)backup log d3 to bak3 within sert b1 values(3,'c')backup log d3 to bak3 withrestore header。nly from bak3 create table b1(c1 int not null,c2 char(10) not null) -Full+Log+Diff backup log d4 to bak4 within sert b1 values(1,'a')backup log d4 to bak4 within sert b1 values(2,'b'

13、)backup database d4 to bak4 with differe ntial, name='d4_diff1'in sert b1 values(3,'c')backup log d4 to bak4 within sert b1 values(4,'d') backup log d4 to bak4 within sert b1 values(5,'d')backup database d4 to bak4 with differe ntial, name='d4_diff2' restore h

14、eadero nly from bak4日志清除1) 如果日志空间被填满，数据库将不能记录修改。2) 数据库在做完全备份时日志被截断。3) 如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录, 不建议使用。4) with truncate_only和with no_log设置日志满时清除日志5) with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要 用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。exec sp_dbopti on d3exec sp_dbopt

15、i onsp_dboption 'd3','tru nc. log on chkpt.','true'-设置自动清除数据库日志sp_dboption 'd3','tru nc. log on chkpt.','false'-将自动清除数据库日志的选项去除 backup log d4 with truncate_only -设置D4日志满时清除日志，并做清除记录backup log d4 with no_log-设置D4日志满时清除日志，但不做清除记录 backup log d4 to bak4 wi

16、th no_truncate-在 D4数据库损坏时马上备份当前数据库日志(DEMO)使用 no_truncate完全+修改1 +差异+修改2+差异+修改3+停止SQL,删除数据库数据文件 +重启SQLbackup log no_trun cate再还原，可还原到修改34) 文件/文件组备份(1) 用于超大型数据库。(2) 只备份选定的文件或者文件组。(3 )必须同时作日志备份。(4) 还原时用文件/文件组备份和日志备份进行还原。(5 )备份量少，恢复速度快。create database d5on primary(n ame=d5_data1,file name='e:datad5d5

17、_data1.mdf,size=2MB),filegroup FG2-创建数据库时创建 filegroup FG2(n ame=d5_data2,filename='e:datad5d5_data2.ndf,-并将文件 d5_data2 放到 FG2 中size=2Mb)log on(n ame=d5_log1,file name='e:datad5d5_log1.ldf,size=2Mb)use d5goalter database d5add file(n ame=d5_data3,file name='e:datad5d5_data5 .n df,size=2MB

18、)to filegroup FG2-将 d5_data3 加到文件组 FG2 中alter database d5 add filegroup FG3 -增加文件组 FG3alter database d5-将 d5_data4 加到文件组 FG2 中add file(n ame=d5_data4,file name='e:datad5d5_data4 .n df,size=2MB)to filegroup FG3sp_helpdb d5filegroup 中create table t1(c1int notnu Il,c2 char(10)notn ull) onprimary-将不

19、同表放到不同create table t2(c1int notnu Il,c2 char(10)notn ull) onFG2create table t3(c1int n otn ull,c2 char(10)notn ull) onFG3backup database d5 to bak5 with in it ,n ame='d5_full'-filegroup 备份backup database d5 filegroup='primary' to bak5 withbackup log d5 to bak5 withbackup database d5

20、filegroup='FG2' to bak5 with backup log d5 to bak5 withbackup database d5 filegroup='FG3' to bak5 with backup log d5 to bak5 withbackup database d5 to bak6 with in it ,n ame='d5_full'-file 备份backup database d5 file='d5_data1' to bak6 withbackup log d5 to bak6 withback

21、up database d5 file='d5_data2' to bak6 withbackup log d5 to bak6 withbackup database d5 file='d5_data3' to bak6 withbackup log d5 to bak6 withbackup database d5 file='d5_data4' to bak6 withbackup log d5 to bak6 withrestore headero nly from bak6SQL还原1、验证备份 restore headero nly

22、from bak3restore filelist only from bak3 with file=1restore label only from bak3restore verify only from bak32、从备份中还原 restore headero nly from bak1restore database d1 from bak1 with file=2-从完全备份中恢复restore headero nly from bak2-从差异备份中恢复restore database d2 from bak2 with file=1, no recoveryrestore dat

23、abase d2 from bak2 with file=5,recoveryrestore headero nly from bak3-从日志备份中恢复restore database d3 from bak3 with file=1, no recoveryrestore logd3 frombak3 with file=2 ,no recoveryrestore logd3 frombak3 with file=3 ,no recoveryrestore logd3 frombak3 with file=4,no recoveryrestore logd3 frombak3 with f

24、ile=5,recoveryrestore database d3 from bak3 with file=1, no recovery-恢复至 U指定时间restore logd3 frombak3 with file=2 ,no recoveryrestore logd3 frombak3 with file=3 ,no recoveryrestore log d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000' restore database d5 filegroup='FG2

25、9; from bak5 with file=4,no recovery - 还原文件组备份 restore log d5 from bak5 with file=5 ,no recoveryrestore log d5 from bak5 with file=7,recoveryrestore headero nly from bak6-还原文件备份restore database d5 file='d5_data3' from bak6 with file=6 ,no recovery restore log d5 from bak6 with file=7 ,no rec

26、overyrestore log d5 from bak6 with file=9,recoveryrestore database d5 from bak6 with replace-删除现有数据库，从备份中重建数据库create database d6-move to将数据库文件移动到新位置on primary(n ame=d6_data,file name='E:Program FilesMicrosoft SQL ServerMSSQLdatad6_Data.MDF', size=2MB)log on(n ame=d6_log,file name='E:Prog

27、ram FilesMicrosoft SQL ServerMSSQLdatad6_log.ldf, size=2MB)gobackupdatabase d6 to bak6 with initdrop database d6restore database d6 from bak6with move 'd6_data' to 'e:datad6d6_data.mdf,move 'd6_log'to 'e:datad6d6_log.ldfsp_helpdb d63、分离与重连接数据库 sp_detach_db 'd6'sp_atta

28、ch_db 'd6','e:datad6d6_data.mdf,'e:datad6d6_log.ldf sp_detach_db d6gocreate database d6on primary(file name='e:datad6d6_data.mdf)for attachgo4、恢复损坏的系统数据库1）先备份 MASTER、MSDB2） 停止SQL服务，将MASTER数据库文件删除或者重命名。这样，SQL服务将不能启动。3）系统数据库的还原（1）如果SQL服务还能启动，则从备份中恢复系统数据库。（2）如果SQL服务不能启动，则需要重建系统数据库。使

29、用SQL文件夹TOOLS'BINN 目录下的 Rebuildm.exe重建master数据库。（3）创建备份设备，指向以前的备份设备。（4） 以单用户模式启动SQLcd programe files'microsoft sql servermssqlb innsqlservr.exe -c -m（5） 进查询分析器，从备份中恢复master数据库。restore database master from masterbakrestore database msdb from disk='e:bakmsdb.bak'MASTER还原后，SQL中用户数据库的信息也会

30、恢复。（6） 如果MASTER没有备份，则需要用sp_attach_db命令将用户数据库附加到新的MASTER 数据库中。全备份+差异备份：以一周为周期，星期日进行全备份，星期一到星期六每天进行差异备份。全备份+日志备份：以一周为周期，星期日进行全备份，星期一到星期六每天进行日志备份。文件组备份+日志备份：备份周期取决于数据库的大小和能力，每周期分别进行一部分数据 文件备份，每天进行日志备份。手动创建备份:使用T_SQL语句创建备份设备。在SQL Server 中使用sp_addumpdevice语句创建备份设备，其语法形式如下:sp_addumpdevice例：创建备份设备磁盘类型，备份设备的逻