如何用SQL备份和还原数据库

如何用SQL备份和还原数据库

如何用SQL备份和还原数据库

教你如何用SQL备份和还原数据库

数据库的备份和恢复

一、备份数据库

1、打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server

2、SQL Server组-->双击打开你的服务器-->双击打开数据库目录

3、选择你的数据库名称（如论坛数据库Forum）-->然后点上面菜单中的工具-->选择备份数据库

4、备份选项选择完全备份，目的中的备份到如果原来有路径和名称则选中名称点删除，然后点添加，如果原来没有路径和名称则直接选择添加，接着指定路径和文件名，指定后点确定返回备份窗口，接着点确定进行备份

二、还原数据库

1、打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server

2、SQL Server组-->双击打开你的服务器-->点图标栏的新建数据库图标，新

建数据库的名字自行取

3、点击新建好的数据库名称-->然后点上面菜单中的工具-->选择恢复数据库

4、在弹出来的窗口中的还原选项中选择从设备-->点选择设备-->点添加-->然后选择你的备份文件名-->添加后点确定返 回，这时候设备栏应该出现您刚才选择的数据库备份文件名，备份号默认为1（如果您对同一个文件做过多次备份，可以点击备份号旁边的查看内容，在复选框中选 择最新的一次备份后点确定）-->然后点击上方常规旁边的选项按钮

5、在出现的窗口中选择在现有数据库上强制还原，以及在恢复完成状态中选择使数据库可以继续运行但无法还原其它事务日志的选项。在窗口的中间部位的将数据 库文件还原为这里要按照你SQL的安装进行设置（也可以指定自己的目录），逻辑文件名不需要改动，移至物理文件名要根据你所恢复的机器情况做改动，如您的 SQL数据库装在D:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data，那么就按照您恢复机器的目录进行相关改动改动，并且最后的文件名最好改成您当前的数据库名（如原来是 zw0001.mdf，现在的数据库是zw0002，就改成zw0002.mdf），日志和数据文件都要按照这样的方式做相关的改动（日志的文件名 是.ldf结尾的），这里的恢复目录您可以自由设置，前提是该目录必须存在（如您可以指定d:\\sqldata\\zw0002.mdf或者d: \\sqldata\\zw0002.ldf），否则恢复将报错

6、修改完成后，点击下面的确定进行恢复，这时会出现一个进度条，提示恢复的进度，恢复完成后系统会自动提示成功，如中间提示报错，请记录下相关的错误内 容并询问对SQL操作比较熟悉的人员，一般的错误无非是目录错误或者文件名重复或者文件名错误或者空间不够或者数据库正在使用中的错误，数据库正在使用的 错误您可以尝试关闭所有关

于SQL窗口然后重新打开进行恢复操作，如果还提示正在使用的错误可以将SQL服务停止然后重起看看，至于上述其它的错误一般都 能按照错误内容做相应改动后即可恢复

三、设定每日自动备份数据库

1、打开企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器

2、然后点上面菜单中的工具-->选择数据库维护计划器

3、下一步选择要进行自动备份的数据-->下一步更新数据优化信息，这里一般不用做选择-->下一步检查数据完整性，也一般不选择

4、下一步指定数据库维护计划，默认的是1周备份一次，点击更改选择每天备份后点确定

5、下一步指定备份的磁盘目录，选择指定目录，如您可以在D盘新建一个目录如：d:\\databak，然后在这里选择使用此目录，如果您的数据库比较多最 好选择为每个数据库建立子目录，然后选择删除早于多少天前的备份，一般设定4－7天，这看您的具体备份要求，备份文件扩展名一般都是bak就用默认的

6、下一步指定事务日志备份计划，看您的需要做选择-->下一步要生成的报表，一般不做选择-->下一步维护计划历史记录，最好用默认的选项-->下一步完成

7、完成后系统很可能会提示Sql Server Agent服务未启动，先点确定完成计划设定，然后找到桌面最右边状态栏中的SQL绿色图标，双击点开，在服务中选择Sql Server

Agent，然后点击运行箭头，选上下方的当启动OS时自动启动服务

8、这个时候数据库计划已经成功的运行了，他将按照您上面的设置进行自动备份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_devicebak2.bak --创建永久磁盘备份设备

exec sp_addumpdevice disk,bak3,e:back_devicebak3.bak

------------------------------------------------------------------------------------------------

exec sp_addumpdevice disk,bak4,\\sv2backupbak4.bak --创建网络永久磁盘备份设备

exec sp_addumpdevice disk,bak5,\\sv2backupbak5.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_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）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。

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:datad5d5_data1.mdf,

size=2MB),

filegroup FG2 --创建数据库时创建filegroup FG2

(name=d5_data2,

filename=e:datad5d5_data2.ndf, size=2Mb)

log on

(name=d5_log1,

filename=e:datad5d5_log1.ldf,

size=2Mb)

use d5

go

alter database d5

add file

并将文件d5_data2放到FG2中

--

(name=d5_data3,

filename=e:datad5d5_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:datad5d5_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 FilesMicrosoft SQL ServerMSSQLdatad6_Data.MDF,

size=2MB)

log on

(name=d6_log,

filename=E:Program FilesMicrosoft SQL ServerMSSQLdatad6_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:datad6d6_data.mdf,

move d6_log to e:datad6d6_log.ldf

sp_helpdb d6

-----------------------------------------------------------------------------------------------

3、分离与重连接数据库

--------------------------------------

sp_detach_db d6

sp_attach_db d6,e:datad6d6_data.mdf,e:datad6d6_log.ldf

--------------------------------------

sp_detach_db d6

go

create database d6

on primary

(filename=e:datad6d6_data.mdf)

for attach

go

-----------------------------------------------------------------------------------------------

4、恢复损坏的系统数据库

-----------------------------------------------------------------------------------------------

1）先备份MASTER、MSDB

2）停止SQL服务，将MASTER数据库文件删除或者重命名。这样，SQL服务将不能启动。

3）系统数据库的还原

-----------------------------------------------

（1）如果SQL服务还能启动，则从备份中恢复系统数据库。

（2）如果SQL服务不能启动，则需要重建系统数据库。

使用SQL文件夹TOOLSBINN目录下的Rebuildm.exe重建master数据库。

（3）创建备份设备，指向以前的备份设备。

（4）以单用户模式启动SQL

cd programe filesmicrosoft sql servermssqlbinn

sqlservr.exe -c -m

（5）进查询分析器，从备份中恢复master数据库。

restore database master from masterbak

restore database msdb from disk=e:bakmsdb.bak

MASTER还原后，SQL中用户数据库的信息也会恢复。

（6）如果MASTER没有备份，则需要用sp_attach_db命令将用户数据库附加到新的MASTER数据库中。

-----------------------------------------------------------------------------------------------

5、自动化备份实现（要将sqlserveragent服务设置为自动启动，并启动该服务）