数据库备份和恢复（一）

以下仅针对数据库SQL Server 2005，解释了数据库备份和恢复相关的基本概念和测试实例。

 

 

（一）可能造成数据库数据损失的原因

①     存储介质故障：保存有数据库文件的磁盘驱动器损坏。

②     用户的错误操作：无意或恶意地在数据库上进行了大量非法操作。

③     服务器彻底崩溃。

 

（二）常见的数据库备份设备

①     磁盘备份设备（disk）：可以是本地磁盘，也可以是网络上的远程磁盘。如果是网络磁盘，则需要使用统一命名方式（UNC）来引用此文件，即//远程服务器名/共享文件名/路径名/文件名。

②     磁带备份设备（tape）：磁带备份不支持远程网络磁带备份。

③     命名管道设备（pipe）：这是微软专门为第三方软件供应商提供的一个备份和恢复方式。

 ④ 逻辑备份设备：即物理备份设备的别名，在数据库备份和还原的时候引用会比较方便。

 

（三）备份方式（具体示例请参考（五）各备份和还原方式示例）

 ① 完整备份

Ⅰ 完整备份是指备份整个数据库。包括数据库中所有的对象和数据。

    Ⅱ 会备份日志中尚未提交的事务日志，以便恢复时使用。

Ⅲ 是其它备份的基准。在实际生产环境中建议不要单独使用完整备份，而是将其与其它备份方式（差异备份、日志备份）结合起来使用。

Ⅳ 只能将数据库恢复到上次备份结束时的状态，不能恢复到指定时刻的状态。

 ② 差异备份

       Ⅰ 在备份前要先进行一次完整备份。

Ⅱ 差异备份会备份最新数据库完整备份以来被修改的数据页。

       Ⅲ 恢复时，指定其中最近的完整备份和其之后一个需要还原的差异备份。

Ⅳ 只能将数据库恢复到上一次差异备份结束时的状态，不能恢复到指定时刻的状态。

③ 日志备份

       Ⅰ 在备份前要先进行一次完整备份。

Ⅱ 日志备份会备份上次日志备份以来的事物日志（递增备份）。

Ⅲ 恢复时，指定其中最近的完整备份和其之后所有的事务日志备份。

Ⅳ 可以将数据库恢复到指定的时间时刻的状态。

④     文件和文件组备份

Ⅰ 可以单独备份组成数据库的文件或文件组。

Ⅱ 需要同时备份事物日志，以便用于还原，否则还原时数据库将一直处于恢复中。

Ⅲ 用于超大型数据库及数据库文件存储在多个磁盘驱动器的情况下。

Ⅳ 只需要恢复遭到破坏的文件或文件组，而不需要恢复整个数据库，从而恢复效率高。

 

（四）恢复模式

 ① 简单恢复模式

       Ⅰ 备份时只备份数据文件（完整备份、差异备份）。

       Ⅱ 只能将数据库恢复到上一次备份结束时的状态。

    Ⅲ 不适用于实际生产环境，可用于开发测试环境。

--将数据库恢复模式设置为简单恢复模式

alter database test set recovery simple

② 完整恢复模式

     Ⅰ 需要备份数据库文件和事务日志文件。

     Ⅱ 此模式下对数据库的所有操作都将写入日志，日志会比较大，对性能可能会有影响。   Ⅲ 可以将数据库恢复到指定时刻的状态（日志备份）。

--将数据库恢复模式设置为完整恢复模式

alter database test set recovery full

③     大容量日志恢复模式

Ⅰ 需要备份数据库文件和事务日志文件。

Ⅱ 不会对所有操作在日志中做全纪录，对大容量操作只进行最小纪录（select into,create index,bcp操作…），只纪录操作的结果，所以日志会比较小，性能会比较好。

Ⅲ 可以恢复到任何备份的结尾，不能将数据库恢复到指定时刻的状态。

--将数据库恢复模式设置为大容量日志恢复模式

alter database test set recovery bulk_logged

 

（五）各备份和还原方式示例

--建立测试数据库test

create database test

GO

use test

--建立测试表

create table ta

(

    id int

)

GO

--创建逻辑备份设备，映射到磁盘文件d:/test.bak

--删除备份设备的语句为sp_dropdevice

exec sp_addumpdevice 'disk','myback','d:/test.bak'

GO

 

①     完整备份及还原

--创建测试数据，插入数据1、2

insert into ta values(1)

insert into ta values(2)

GO

--完整备份

backup database test

to myback

with init--初始化备份设备

GO

 

--从完整备份中覆盖现有数据库

restore database test

from myback

with replace—-如果不用replace的话需要在还原前先备份日志尾部

GO

--以下为在完全备份还原前先备份日志尾部再还原的示例

--先备份日志尾部

backup log test

to myback

with norecovery

GO

--备份完日志尾部后从完整备份中还原数据库

restore database test

from myback

 

②     差异备份及还原

use test

GO

--删除测试数据

delete from ta

GO

--插入测试数据1

insert into ta values(1)

GO

--先进行完整备份

backup database test

to myback

with init--初始化备份设备

GO

--插入测试数据

insert into ta values(2)

GO

--差异备份1

backup database test

to myback

with differential

GO

--插入测试数据

insert into ta values(3)

GO

--差异备份2

backup database test

to myback

with differential

GO

--先还原完全备份

restore database test

from myback

with replace,file = 1,norecovery

GO

--再还原差异备份,可以指定其中一个差异备份(这里只还原到差异备份1)

restore database test

from myback

with file = 2,recovery

GO

 

③     日志备份及还原

use test

GO

--删除测试数据

delete from ta

GO

--插入测试数据

insert into ta values(1)

GO

--先进行完整备份

backup database test

to myback

with init--初始化备份设备

GO

--插入测试数据

insert into ta values(2)

GO

--日志备份1

backup log test

to myback

GO

--插入测试数据

insert into ta values(3)

GO

--日志备份2

backup log test

to myback

GO

--先恢复完整备份(如果不用replace，那么需要先备份尾日志)

restore database test

from myback

with replace,file = 1,norecovery

GO

--恢复第一个日志备份

restore database test

from myback

with file = 2,norecovery

Go

--恢复第二个日志备份（可以使用stopat语句将数据库还原到指定时刻）

restore database test

from myback

with file = 3,recovery

 

④        文件和文件组备份及还原

之前创建的数据库只有一个文件组primary，那么现在再创建一个辅助文件组fg，并且在上面创建一个表tb。

--增加一个文件组fg

alter database test add filegroup fg

GO

alter database test

add file--在文件组fg上追加辅助文件

(

    name = 'test01',

    filename = 'd:/test01.ndf'

)to filegroup fg

GO

--在文件组fg上创建表tb

create table tb

(

    id int

)on fg

GO

--第一次主文件组也要备份

backup database test

    filegroup = 'primary',

    filegroup = 'fg'

to myback

with init

GO

--创建测试数据

insert into tb values(1)

GO

--备份文件组fg

backup  database test

    filegroup = 'fg'

to myback

GO

--备份尾日志

backup log test

to myback

with norecovery

GO

--还原文件组fg

restore database test

from myback

with file = 2,norecovery

GO

--还原尾日志（发现还原一个辅助文件组再还原尾日志能够将主文件组也能还原）

restore database test

from myback

with file = 3,recovery

 

(六)备份和还原其他事项

① 获取媒体集和备份设备以及验证备份集信息语句

--包括所有备份标头信息

restore headeronly from myback

--包括备份集中包含的一组数据库和日志文件

restore filelistonly from myback

--包含有关给定备份设备标识的备份媒体的信息。

restore labelonly from myback

--检查备份集是否已完成以及整个备份是否可读

restore verifyonly from myback

 

② 可以同时将数据库备份到跨多个磁盘的一个媒体集中。

backup database test

to  disk = 'd:/test01.bak',

    disk = 'e:/test02.bak',

    disk = 'f:/test03.bak'

with format

③系统数据库也要进行备份。建议每次修改master数据库后都要将其备份。