2023年7月30日发(作者：)

论文题目：SQL Server数据库数据恢复与备份

SQL Server 数据库数据备份与恢复

摘要：本文着重解决异常情况下数据库的备份与恢复、数据库suspect问题、数据库快速备份与数据压缩等问题。

关键词：SQL Server；备份；恢复；BCP

1 SQL Server数据库备份与恢复的基本功能

1.1 SQL Server数据库的备份

数据库的备份就是制作数据库结构和数据的拷贝以便在数据库遭到破坏的时候能够修复数据库。在 SQLServer系统中备份是动态的，即在进行备份的时候允许用户对数据库继续进行操作，SQL Server提供了满足企业和数据库活动的各种需要的四种备份方法：

(1) 数据库备份：数据库的完整备份，将拷贝所有用户定义的对象、系统表和数据 (2) 增量备份：一种更小、更快的备份，仅备份从上一次完全数据库备份之后的已经改变的数据库部分和备份在执行增量备份过程中发生的任何活动以及事务日志中任何未提交的事务。数据库的完整备份是用作系统失败时的基线，只有在执行了完全数据库备份之后才能执行增量备份

(3) 事务日志备份：一种特殊的增量备份，备份的目标是面向各种事务日志

(4) 文件/文件组备份：当数据库非常巨大时，可以执行数据库文件或文件组备份

以上的各种备份方法在SQL Server 7.0下使用Transact-SQL 的 Backup Database 命令来完成。SQLServer 6.5下使用Dump命令来完成，或直接在SQL ServerEnterprise Manager图形界面下完成

1.2 SQL Server数据库的恢复

数据库恢复就是复原一个被破坏、出错或丢失的数据库的过程。在进行数据库恢复时，系统首先进行一些操作，以确保数据库安全和迅速地恢复，同时必须确保数据库备份文件是有效的，并且在备份文件中包含了所需要的备份内容。数据库恢复是静态的，恢复时不允许用户继续操作数据库。当使用完全数据库备份恢复时，系统将自动重建原来的所有数据库文件，并且把这些文件放在备份时的原始位置，这种过程是系统自动完成的。因此，用户在执行数据库恢复工作时，不需要重新建立数据库结构数据库的恢复可以通过SQL Server Enterprise Manager图形界面中，选择Tools 然后选择Restore Database来完成或使用Transact-SQL的 Restore命令完成。

对于关键应用的数据库系统，应根据具体环境和条件制订一个完善可行、确保系统安全的备份计划，没有备份的系统是不可靠、危险的，同时后果将是严重的，破坏性病毒 误操作 自然灾害等等，都可能会对数据库系统造成难以估量的破坏。以下我们将主要介绍一些突发情况下SQL Server数据库备份与恢复的实用技术。

2 SQL Server数据库备份与恢复的实用技术 2.1 进入SQL Server单用户模式

单用户模式可以理解为一种数据库的保护模式，许多数据库的关键操作，如数据库系统级恢复都必须在单用户模式下完成。进入单用户模式是以下所介绍的各项实用技术的基础，在NT环境下，可以有多种方法进入SQL Server单用户模式 最简单的方法是

(1) 打开 “控制面板--服务” 选中MSSQLSERVER服务。

(2) 点 “停止” 停止MSSQLSERVER服务

(3) 在“启动参数” 中 输入-m 点“开始” 以单用户模式启动SQL Server

按照上述步骤重来一次，不需要任何启动参数，就能从单用户模式切换到正常使用的多用户模式。

2.2 数据库的快速备份、压缩与恢复

BCP是SQL Server中联系数据库表和外部文件的桥梁，它能方便地将外部文件按一定格式快速、成批地导入数据库，同时也能快速地导出为普通数据文件。因此可以利用这种方法对数据库进行快速备份、压缩与恢复 BCP的语法如下：

BCP dbtable {in | out} datafile

[-m maxerrors] [-f formatfile ] [-e errfile] [ -FFirstrow] [ -L lastrow]

[-b batchsize][ -n native type] [-c character type][-q quoted identifier]

[ -t field terminator][ -r row terminator][ -i inputfile]

[-o outfile ][-a packetsize][ -E explicit identity ][-U username]

[ -P password ][-S server][ -v version ][ -T trusteduconnection]

基本参数说明如下：

(1) dbtable 导入或导出的数据库表名

(2) {in | out} in为导入 out为导出

(3) datafile 导入或导出的外部文件名 (4) -f formatfile 与数据库表一致的格式文件 格式文件可以通过 BCP 生成

示例

文件data导入数据库Today Table表中

C: > BCP Today Table in data -f -U sa -P -SServer

将表Today Table导出为外部文件data

C: > BCP Today Table out data -f -U sa -P -SServer

对一个数据库的所有数据表进行导出,必须产生上述的BCP命令(多个) 以下是在SQL

Server中由查询语句一次生成所有的命令，然后将查询的结果放入一个批处理文件(.bat)即可：

Select 'BCP database.'++'.' + + ' out ' + 'backup/'

+ + '.out ' + '-n -SServername -Usa -Ppassword'

From database..sysobjects a, database..sysusers b

Where ( = ) and ( = 'U')

恢复数据批处理文件：

select 'BCP database.' + + '.' + + ' in ' +'backup/'

+ + '.out ' + '-n -SServername -Usa -Ppassword'

from database..sysobjects a, database..sysusers b

where ( = ) and ( = 'U')

将上面database换成自己需要备份的数据库名称，将servername改为相应的SQL Server服务器名，将password替换成实际sa密码，上面查询语句在SQL Server 6.5的ISQL_w中执行(SQL Server 7.0的Query Analyzer)，然后将执行结果通过剪贴板Copy到批处理文件(.bat)。在执行批处理文件之前，需要在批处理文件所在路径下建立Backup目录，*.out备份文件将生成到此子目录下，对生成的备份文件可以根据需要进行压缩和备份。 2.3 使用数据库设备文件进行数据库的恢复

与Fox Pro Access这些基于文件的桌面数据库有本质不同，SQL Server数据库是基于设备的大型数据库系统(数据库建立在设备上) 性能提高的同时，也增加了管理和维护的难度，当数据库不能正常启动、系统面临重新安装、数据面临全部丢失时，尝试利用数据库设备文件来恢复数据库中的数据将是行之有效的“救命稻草” 有以下两种方法来进行恢复。

2.3.1 方法一

操作步骤

(1) 备份需要恢复的数据库设备文件，包括日志所在设备 (若SQL Server服务已启动 数据库设备文件将处于保护状态，不能拷贝 必须首先停止SQL Server服务)。

(2) 找一个运行正常的SQL Server数据库服务器，或在本机重新安装SQL Server服务器。

(3) 关闭SQL Server的所有应用程序，停止SQLServer的所有服务，包括SQL Executive

然后将备份文件拷贝到该服务器上

(4) 以单用户模式启动SQL Server 启动ISQL_w 以sa登录。

(5) 运行disk reinit命令 恢复数据库及日志设备，然后运行disk refit命令，恢复数据库(6) 关闭ISQL_w 停止SQL Server服务。

(7) 以正常模式运行SQL Server 此时数据库设备中的数据库已经恢复。

说明

单用户模式下 运行disk reinit是关键 命令格式

如下

DISK REINIT

NAME = 'logical_name'

PHYSNAME = 'physical_name'

VDEVNO = virtual_device_number SIZE = number_of_2K_blocks

VSTART = virtual_address

其中NAME为数据库设备的名字 “PHYSNAME”是数据库设备文件的位置， VDEVNO 是数据库设备的编号可以将“1-255”任意的整数值分配给这个设备，但必须保证唯一 SQL Server已将0分配给master设备 SIZE是该文件的大小除以2K的值 例如1M的数据库设备文件SIZE应为 512 VSTART 通常忽略 例如。

DISK REINIT

NAME = 'DEVICE5'

PHYSNAME = 'c sqldata '

VDEVNO = 5

SIZE = 25600

这个例子将c sqldata 挂在系统上并命名为 DEVICE5 该文件的大小为

25600*2K=51200K=50M 需注意的是，SIZE一定要算对，如果这里给出的值与文件的实际大小不符 将引起不可预料的后果。

Disk reinit命令仅仅在系统中建立了数据库设备要想恢复设备中包含的数据库 就要用到disk refit命令，该命令没有参数 必须在单用户模式下运行。

利用这种方法能完整的恢复数据库中的数据 但日志设备的空间与备份前有所不同。

2.3.2 方法二

操作步骤

(1) 备份需要恢复或拷贝的数据库设备文件(数据和日志)。

(2) 在运行正常的数据库服务器中按照相同的大小建立新数据库，名字可以不同。

(3) 停止当前服务器SQL Server服务。

(4) 用备份的数据库设备文件覆盖新建的数据库设备文件。 (5) 启动SQL Server服务进程。

2.4 解决数据库Suspect问题

当NT系统异常断电时，经常会引起数据库系统的工作异常，如使数据库的状态变为可疑 suspect，这意味着该数据库对外不能使用，以往的解决办法只能是重新安装或通过数据备份来进行恢复，以下我们将介绍一个简单的恢复办法 操作步骤如下

(1) 以单用户模式运行SQL Server。

(2) 启动ISQL_w 以sa登录

(3) 在Master数据库下执行

select * from sysdatabases

查看sysdatabases里status字段大于256的记录，若某数据库处在suspect状态，那么它的status一定大于256将这个值用SQL语句(Update)改为X-256 X为原来的值

Update sysdatabase set status=status-256 wherename=databasename

其中databasename为当前状态可疑的数据库名。

(4) 重新启动SQL Server 数据库应恢复为正常状态

说明

某数据库状态可疑(Suspect) 并不意味着数据库管理系统(如Enterprise Manager)不能使用。

在数据库出现异常情况时，备份将显得极为重要，因为数据库管理系统的结构信息均存放在master数据库中。

2.5 系统数据库的恢复

在SQL Server数据库中，系统信息存储在系统数据库中 ，主要的系统数据库包括master

model msdb 如果系统配置了复制 那么还包括系统数据库distribution在这些系统数据库中，有许多系统表，这些系统表记录了系统的有关信息，如果包含系统数据库的介质被破坏了那么就必须重建系统数据库，当系统数据库被破坏时，但是仍然可以启动SQL Server服务，那么可以从有效的系统数据库备份中恢复数据库，如果master数据库被破坏了，那么就无法启动系统，这时，需要重建系统数据库可以按照下列步骤重建和恢复数据库。

(1) 重新建立系统数据库，在SQL Server 7中，提供了一个命令行实用程序

可以用来重建系统数据库。

(2) 当系统数据库重建之后 可以启动SQL Server服务。

(3) SQL Server服务启动之后，这时的系统数据库是空的，这里的“空”是指系统表中不包含原有数据库“设备” 帐户等相关信息。因此， 需要从系统数据库的备份中恢复数据库，在恢复系统数据库时，一般地，应该首先恢复master数据库 其次是msdb

数据库和distribution数据库 最后恢复 model 数据库。

参考文献

[1] 许春艳. SQL Server 2005数据库恢复技术探索[J]. 硅谷. 2011(08)

[2] 尹秀梅. 数据库恢复研究[J]. 中国科技信息. 2014(16)

[3] 陈益全,蔡泽利. 浅谈数据库恢复技术[J]. 科技传播. 2010(17)

[4] 习贵民. 数据库恢复技术[J]. 渭南师范学院学报. 2002(S1)

[5] 于秀云. 对数据库恢复策略的性能分析[J]. 计算机研究与发展. 1998(12)