SQL备份与还原经典文档,很实用

sjaoyh

我们在使用SQL时,经常会用到备份或还原,以下是整理出来的经典语句:

SQL备份与还原经典文档
===================================================================================
SQL备份
-----------------------------------------------------------------------------------
1、SQL数据库恢复模型
-----------------------------------------------------------------------------------
1）完全恢复模型
-----------------
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。
（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。
------------------
（2）大容量日志记录恢复模型
------------------
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。
（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。
-------------------
（3）简单恢复模型
-------------------
（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。
（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。
（3）不适于生产系统和大规模*作环境下选用。
-----------------------------------------
alter database d1 set recovery simple        --设置数据库恢复模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
----------------------------------------------------------------------------------
2、备份设备
----------------------------------------------------------------------------------
1）物理设备
---------------------------
disk：支持本地磁盘或者网络备份
tape：支持磁带机备份
name pipe：支持第三方备份软件
---------------------------
2）逻辑设备
---------------------------
永久备份文件：可以重复使用，应该在备份前创建。
临时备份文件：用于一次性备份，在备份时创建。
-------------------------------------------------
exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak'    --创建永久磁盘备份设备
exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak'
------------------------------------------------------------------------------------------------
exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak'      --创建网络永久磁盘备份设备
exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak'
------------------------------------------------------------------------------------------------
exec sp_dropdevice 'bak5'                                        --删除备份设备
------------------------------------------------------------------------------------------------
backup database d3 to bak3            --将数据库备份到备份设备
backup database d4 to bak4
------------------------------------------------------------------------------------------------
restore headeronly from bak2          --查看备份设备中的内容
------------------------------------------------------------------------------------------------
backup database d3 to disk='e:\back_file\d3.bak'        --将数据库备份到临时备份文件
backup database d4 to disk='e:\back_file\d4.bak'
------------------------------------------------------------------------------------------------
restore database d3 from bak3                          --从备份设备还原数据库               
restore database d4 from disk='e:\back_file\d4.bak'    --从备份文件还原数据库
------------------------------------------------------------------------------------------------
3、使用多个备份文件存储备份
----------------------------------------------------------------------
1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。
2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。
3）媒体集中的文件必须同时使用，而不能单独使用。
4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。
----------------------------------------------------------------------
backup database d4 to bak4,bak5,bak6 with medianame='bak456',format    --备份D4并形成Media Set
backup database d3 to bak4                --失败，因Media set中文件必须同时使用
backup database d3 to bak4,bak5,bak6      --成功，将D3也备份到Media Set中
restore headeronly from bak4,bak5,bak6    --查看Media Set中的备份内容
------------------------------------------------------------------------------------------------
backup database d4 to bak4 with medianame='bak4',format          --重新划分Media Set
backup database d3 to bak5,bak6 with medianame='bak56',format
-----------------------------------------------------------------------------------------------
backup database d1 to bak1 with init        --with init重写备份设备中内容
backup database d2 to bak1 with noinit      --with noinit将内容追加到备份设备中
restore headeronly from bak1
-----------------------------------------------------------------------------------------------
4、备份的方法
----------------------------------------------------------------------------------------------
1）完全备份
-------------------------------------------
（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。
（2）完全备份会备份数据库的所有数据文件、数据对象和数据。
（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。
--------------------------------------------
backup database d1 to bak1 with init        --完全备份
backup database d1 to bak1 with noinit
-----------------------------------------------------------------------------------------------
2）差异备份
---------------------------------------------
（1）基于完全备份。
（2）备份自最近一次完全备份以来的所有数据库改变。
（3）恢复时，只应用最近一次完全备份和最新的差异备份。
-----------------------------------------------
backup database d2 to bak2 with init,name='d2_full'      --差异备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup database d2 to bak2 with differential,name='d2_diff1'
insert b1 values(1,'a')
backup database d2 to bak2 with differential,name='d2_diff2'
insert b1 values(2,'b')
backup database d2 to bak2 with differential,name='d2_diff3'
insert b1 values(3,'c')
backup database d2 to bak2 with differential,name='d2_diff4'
restore headeronly from bak2
----------------------------------------------------------------------------------------------
3）事务日志备份
-------------------------------------------------------------
（1）基于完全备份。
（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。
（3）允许恢复到故障时刻或者一个强制时间点。
（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。
-------------------------------------------------------------
backup database d3 to bak3 with init,name='d3_full'      --日志备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup log d3 to bak3 with name='d3_log1'
insert b1 values(1,'a')
backup log d3 to bak3 with name='d3_log2'
insert b1 values(2,'b')
backup log d3 to bak3 with name='d3_log3'
insert b1 values(3,'c')
backup log d3 to bak3 with name='d3_log4'
restore headeronly from bak3
-----------------------------------------------------------------------------------------------
create table b1(c1 int not null,c2 char(10) not null)          --Full+Log+Diff
backup log d4 to bak4 with name='d4_log1'
insert b1 values(1,'a')
backup log d4 to bak4 with name='d4_log2'
insert b1 values(2,'b')
backup database d4 to bak4 with differential,name='d4_diff1'
insert b1 values(3,'c')
backup log d4 to bak4 with name='d4_log3'
insert b1 values(4,'d')
backup log d4 to bak4 with name='d4_log4'
insert b1 values(5,'d')
backup database d4 to bak4 with differential,name='d4_diff2'
restore headeronly from bak4
-----------------------------------------------------------------------------------------------
日志清除
-----------------------------------------
1）如果日志空间被填满，数据库将不能记录修改。
2）数据库在做完全备份时日志被截断。
3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。
4）with truncate_only和with no_log设置日志满时清除日志
5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。
-------------------------------------------
exec sp_dboption d3
exec sp_dboption
sp_dboption 'd3','trunc. log on chkpt.','true'        --设置自动清除数据库日志
sp_dboption 'd3','trunc. log on chkpt.','false'        --将自动清除数据库日志的选项去除
-----------------------------------------------------------------------------------------------
backup log d4 with truncate_only          --设置D4日志满时清除日志，并做清除记录
-----------------------------------------------------------------------------------------------
backup log d4 with no_log                --设置D4日志满时清除日志，但不做清除记录
-----------------------------------------------------------------------------------------------
backup log d4 to bak4 with no_truncate    --在D4数据库损坏时马上备份当前数据库日志(DEMO)
--------
使用no_truncate
完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL
backup log no_truncate
再还原，可还原到修改3
-----------------------------------------------------------------------------------------------
4）文件/文件组备份
------------------------------------------------------------------
（1）用于超大型数据库。
（2）只备份选定的文件或者文件组。
（3）必须同时作日志备份。
（4）还原时用文件/文件组备份和日志备份进行还原。
（5）备份量少，恢复速度快。
------------------------------------------------------------------
create database d5                     
on primary
(name=d5_data1,
filename='e:\data\d5\d5_data1.mdf',
size=2MB),
filegroup FG2                              --创建数据库时创建filegroup FG2
(name=d5_data2,
filename='e:\data\d5\d5_data2.ndf',        --并将文件d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:\data\d5\d5_log1.ldf',
size=2Mb)
use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:\data\d5\d5_data5.ndf',
size=2MB)
to filegroup FG2                          --将d5_data3加到文件组FG2中
alter database d5 add filegroup FG3        --增加文件组FG3
alter database d5                          --将d5_data4加到文件组FG2中
add file
(name=d5_data4,
filename='e:\data\d5\d5_data4.ndf',
size=2MB)
to filegroup FG3
sp_helpdb d5
create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中
create table t2(c1 int not null,c2 char(10) not null) on FG2
create table t3(c1 int not null,c2 char(10) not null) on FG3
----------------------------------------------------------------------------------------------
backup database d5 to bak5 with init,name='d5_full'                  --filegroup备份
backup database d5 filegroup='primary' to bak5 with name='d5_primary'
backup log d5 to bak5 with name='d5_log1'
backup database d5 filegroup='FG2' to bak5 with name='d5_FG2'
backup log d5 to bak5 with name='d5_log2'
backup database d5 filegroup='FG3' to bak5 with name='d5_FG3'
backup log d5 to bak5 with name='d5_log3'
----------------------------------------------------------------------------------------------
backup database d5 to bak6 with init,name='d5_full'                  --file备份
backup database d5 file='d5_data1' to bak6 with name='d5_data1'
backup log d5 to bak6 with name='d5_log1'
backup database d5 file='d5_data2' to bak6 with name='d5_data2'
backup log d5 to bak6 with name='d5_log2'
backup database d5 file='d5_data3' to bak6 with name='d5_data3'
backup log d5 to bak6 with name='d5_log3'
backup database d5 file='d5_data4' to bak6 with name='d5_data4'
backup log d5 to bak6 with name='d5_log4'
restore headeronly from bak6
===============================================================================================
SQL还原
===============================================================================================
1、验证备份
------------------------------------------------------------
restore headeronly from bak3
restore filelistonly from bak3 with file=1
restore labelonly from bak3
restore verifyonly from bak3
-----------------------------------------------------------------------------------------------
2、从备份中还原
-----------------------------------------------------------------------------------------------
restore headeronly from bak1
restore database d1 from bak1 with file=2                        --从完全备份中恢复
-----------------------------------------------------------------------------------------------
restore headeronly from bak2                                      --从差异备份中恢复
restore database d2 from bak2 with file=1,norecovery            
restore database d2 from bak2 with file=5,recovery
-----------------------------------------------------------------------------------------------
restore headeronly from bak3                                      --从日志备份中恢复
restore database d3 from bak3 with file=1,norecovery
restore log        d3 from bak3 with file=2,norecovery
restore log        d3 from bak3 with file=3,norecovery
restore log        d3 from bak3 with file=4,norecovery
restore log        d3 from bak3 with file=5,recovery
-----------------------------------------------------------------------------------------------
restore database d3 from bak3 with file=1,norecovery              --恢复到指定时间
restore log        d3 from bak3 with file=2,norecovery
restore log        d3 from bak3 with file=3,norecovery
restore log        d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000'
-----------------------------------------------------------------------------------------------
restore database d5 filegroup='FG2' from bak5 with file=4,norecovery    --还原文件组备份
restore log d5 from bak5 with file=5,norecovery
restore log d5 from bak5 with file=7,recovery
-----------------------------------------------------------------------------------------------
restore headeronly from bak6                                            --还原文件备份
restore database d5 file='d5_data3' from bak6 with file=6,norecovery
restore log d5 from bak6 with file=7,norecovery
restore log d5 from bak6 with file=9,recovery
-----------------------------------------------------------------------------------------------
restore database d5 from bak6 with replace          --删除现有数据库，从备份中重建数据库
-----------------------------------------------------------------------------------------------
create database d6                                  --move to将数据库文件移动到新位置
on primary
(name=d6_data,
filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_Data.MDF',
size=2MB)
log on
(name=d6_log,
filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_log.ldf',
size=2MB)
go
backup    database d6 to bak6 with init
drop database d6
restore database d6 from bak6
with move 'd6_data' to 'e:\data\d6\d6_data.mdf',
      move 'd6_log'    to 'e:\data\d6\d6_log.ldf'
sp_helpdb d6
-----------------------------------------------------------------------------------------------
3、分离与重连接数据库
--------------------------------------
sp_detach_db 'd6'                           
sp_attach_db 'd6','e:\data\d6\d6_data.mdf','e:\data\d6\d6_log.ldf'
--------------------------------------
sp_detach_db d6
go
create database d6
on primary
(filename='e:\data\d6\d6_data.mdf')
for attach
go
-----------------------------------------------------------------------------------------------
4、恢复损坏的系统数据库
-----------------------------------------------------------------------------------------------
1）先备份MASTER、MSDB
2）停止SQL服务，将MASTER数据库文件删除或者重命名。这样，SQL服务将不能启动。
3）系统数据库的还原
-----------------------------------------------
（1）如果SQL服务还能启动，则从备份中恢复系统数据库。
（2）如果SQL服务不能启动，则需要重建系统数据库。
      使用SQL文件夹TOOLS\BINN目录下的Rebuildm.exe重建master数据库。
（3）创建备份设备，指向以前的备份设备。
（4）以单用户模式启动SQL
      cd programe files\microsoft sql server\mssql\binn
      sqlservr.exe -c -m
（5）进查询分析器，从备份中恢复master数据库。
      restore database master from masterbak
      restore database msdb from disk='e:\bak\msdb.bak'
      MASTER还原后，SQL中用户数据库的信息也会恢复。
（6）如果MASTER没有备份，则需要用sp_attach_db命令将用户数据库附加到新的MASTER数据库中。
-----------------------------------------------------------------------------------------------
5、自动化备份实现（要将sqlserveragent服务设置为自动启动，并启动该服务）

cyqw

不要被这么多命令吓着了，可以用企业管理器完成大部分的操作。
当然有些操作不能用企业管理器完成的就需要这些命令了。
可以保留下来作为资料使用。

sjaoyh

是啊,处理问题和解决问题有多种方法,只要找到最适用的就可以了

Richard_Chen

呵，这么多的命令会让很多人对SQL不熟悉的人看晕了。
如果对对SQL不太熟悉，想做备份的话，最简单而且有效的做法是：打开企业管理器，并依次打开“管理”--“数据库维护计划”--右键“新建维护计划”，然后根据向导，一步步制订一份包括数据库完整性检查、定期备份等的相对完整的维护计划。

wangbing1980

新手来学习学习哟，谁有传点上来啊

xieting707

很详细的关于SQL备份的资料，值得收藏。
不过建议初学者就不用理会这些资料了，会把头弄晕的。

yanghutom

好东西！~~学习学习！~~谢谢楼主

gmg-cs

详尽的解说！ ，收了温故备用！

lx3954

很好的学习文档啊，适合新手，谢谢提供