sqlserver2005备份与恢复介绍

王爱东 · 发表于 2020-5-21 17:33:48

一、目的说明

数据库备份是维护数据正确，防止数据丢失的重要手段。备份点恢复策略可以让数据还原到备份点，通过触发备份的方法，可以提高备份的有效性；采用即时点备份策略，可将数据恢复到任意点，提高数据的安全性；通过性能警报监视事务日志使用情况，及时优化日志文件，可以提高系统性能。

二、备份数据库步骤

1、第一步：在开始—>程序(P)—>MicrosoftSQLserver2005—>SQLServerManagementStudio（如下图）

2、第二步：登录数据库服务器，在打开企业管理器后，逐一展开，找到并展开“数据库”这一项，然后会看到需要备份的数据库“gxbtoc”（如下图）

3、第三步：选择要备份的数据库单击右键（如下图）

4、第四步：点击上图的“备份”，进入下图（一般情况用默认设置就可以了）（如下图）

5、第五步：单击上图的“添加”,进入“选择备份目标”对话框（如下图）

6、第六步：点击上图“选择备份目标”右边的三点框打开备份文件的路径，见下图，在“定位数据库文件”窗口指定目录并填写备份的文件名，然后点确定，返回“备份目录”窗口。

7、第七步：点击确实返回“备份目录”后再点击“确定”后，即开始备份（如下图）

8、第八步：打开步骤五中确定的那个盘符就可以看到备份的数据库（如下图）

三、还原数据库步骤

1、第一步：在开始—>程序集(P)—>MicrosoftSQLserver2005—>SQLServerManagementStudio（如下图）

2、第二步：登录需要备份的数据库服务器，打开企业管理器后，逐一展开，看到有数据库项后，选中数据库节点单击右键（如下图）

3、第三步：选择“还原”后进入“还原数据库”窗口，目标数据库默认已经选择需要还原的数据，接着在“还原的源”中的选择“源设备(D)”，单击“源设备((D)”右边的“…”按钮.见下图

4、进入“指定备份对话框”，点击“添加”，进入指定数据库文件窗口。

5、第四步：浏览并找到需要还原的数据库备份文件，点击“确定”

6、第六步：点击“确定”后回到指“定备份”对话框，此时需要还原的文件已经列出来了，接着点击“确定”，返回“还原数据库”。

注：有些备份文件有多个备份集，可根据实际情况选择备份集还原数据库。

8、在左边的功能导航框中选择“选项”并选择“在现有数据库上强制还原(F)”点击“确定”。

9、系统自动还原数据库，成功后系统提示还原数据库成功，点击确定或OK即可。

四、数据库数据恢复步骤

1、通过日志恢复SQLSERVER2005数据(也可通过LogExplorer工具查找进行恢复数据)

(A)......通过日志和时间点来恢复数据的前提条件：数据库的故障恢复改为非简单模式，去掉自动关闭和自动收缩两个选项，如果是简单模式：类似下面的语句操作数据就不会记录到日志中：select*intotfrom[表名].【采用LogExplorer工具可以在线操作，通过恢复日志（指定时间点恢复）来恢复数据，必须停止数据库或者再另一个数据库恢复（前提是必须有一个完全备份和日志备份）】

这时为保证数据的完整要将数据库的恢复模式改成“完整.

1.1、这时对数据库事务日志做备份（注意，如果没做个数据库完整备份，是不能做事务日志备份的）

这时新建一个数据库zp（将以前的数据库改名），恢复数据库

这时我们看到，有两个还原的数据库备份，因为我对zp数据库备份了两次，两次的备份的数据文件都一样。这里我们选择最近时间的备份默认在数据库的设置如下：是追加到备份集里，所以会有两个备份，如下图：

同时，在”选项“里设置”不回滚“事务，

注意：通过事务日志还原数据库，必须选择"不回滚"事务

确定后：出现下面情况：

这时发现，数据库一直是”正在还原“，这时还原数据库事务日志，

1.2、“常规”里选择时间，（刚删除的时间）

1.3、“选项”里将恢复状态设置为”回滚未提交“事务

确定后，查询数据库，发现数据回来了.

2、无日志的数据恢复

2.1.新建一个同名的数据库
2.2再停掉sqlserver(注意不要分离数据库)
2.3用原数据库的数据文件覆盖掉这个新建的数据库
2.4再重启sqlserver
2.5此时打开企业管理器时会出现置疑，先不管，执行下面的语句（注意修改其中的数据库名)
2.6完成后一般就可以访问数据库中的数据了，这时，数据库本身一般还要问题，解决办法是，利用
数据库的脚本创建一个新的数据库，并将数据导进去就行了.
USEMASTER
SP_CONFIGURE'ALLOWUPDATES'，1RECONFIGUREWITHOVERRIDE
UPDATESYSDATABASESSETSTATUS=32768WHERENAME='数据库名'
sp_dboption'数据库名'，'singleuser'，'true'
DBCCCHECKDB('数据库名')
updatesysdatabasessetstatus=28wherename='数据库名'
sp_configure'allowupdates'，0reconfigurewithoverride
sp_dboption'数据库名'，'singleuser'，'false'

五、数据库压缩

1、执行M_CompressDataBase_T存储过程对非活动的服务器的所有数据库进行压缩.

六、常见异常处理

1、磁盘空间不够

解决方法：扩充磁盘空间或选择空间足够的磁盘

2、如果还原后，遇到SQL问题：错误21002:[sql-dmo]用户“***”已经存在错误。

解决方法：1.打开SQLSERVER2005Management→数据库→展开出问题的数据库如"mydb"→"用户"→在右侧窗口中选择出问题的用户名如:"***"→右击鼠标→删除

3、在还原数据库时遇到＂在数据还原时候出现＂试图扩大物理文件时，MODIFYFILE遇到操作系统错误112(磁盘空间不足)。未能调整文件xxx_log'的空间分配。RESTOREDATABASE操作异常终止.＂的问题．

解决方法：运行cmd输入：convert盘符：/FS：NTFS如：convertC:/FS:NTFS

4、无法将设备'xxx'上的备份应用于数据库'yyy'，restoredatabase操作异常终止.

解决方法：更改还原数据库名

5、问题5：恢复过程中出现“因为数据库正在使用，所以未能获得对数据库的排它访问权。”

解决方法：出现这种问题主要是由于数据库的会话还没有中断，可以采用kill会话进程或重启服务器

6、为备份或还原操作指定的设备太多；只允许指定64个设备。RESTOREHEADERONLY操作异常终止。(MicrosoftSQLServer，Error:3205)

解决方法：如果系统同时安装了sqlserver2000跟sqlserver2005，在还原时可能会出现上述错误，解决方法---于数据库属性-选项-兼容级别->sqlsever2000(80)，或在安装时为sqlserver2000跟sqlserver2005一个或两个都指定实例名.

7、问题3：无法更改数据库中对象的所有者：
解决方法：使用如下代码：
execsp_changeobjectowner'原所有者名'，[dbo]
执行存储过程changename
execchangename'原所有者名'，'新所有者名'

8、问题7：如何将SQLSERVER数据库备份到网络上

解决方法：

[@physicalname=]'physical_name'

备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。physical_name的数据类型为nvarchar(260)，没有默认值，并且不能为NULL。

当创建远程网络位置上的备份设备时，请确保在其下启动SQLServer的名称对远程的计算机有适当的写入能力。

B.添加网络磁盘备份设备
下面的示例显示一个远程磁盘备份设备。在其下启动SQLServer的名称必须对该远程文件拥有权限。

USEmasterEXECsp_addumpdevice'disk'，'networkdevice'，'\\servername\sharename\path\filename.ext'"
以前曾看到过有人问，怎么样才能将数据备份到网络上，一直没有答案，然后我就试验了一把.
在服务器上建了一个目录，然后确保共享并有权限，如果需要建在共享目录下的目录下，那还得保证用户有权限
当然，SQLSERVER系统启动早于登录，一般用户会在登录中使用本地系统账户，我想在登录到系统之前一直是administrator用户模拟，因此，我将登录的账户改为了网络用户，因为我公司有域，因此我将域用户设置为本机的系统管理员，以确保该服务能启动.
接下来更简单了:
sp_addumpdevice'DISK'，'NetWork'，'\\server\d$\databak\xuzh.bak'gobackupdatabasexuzhtonetwork
得到的结果如下:
成功完成网络备份

七、总结

1、这是一般大型网站数据安全的一个办法，因为数据库比较大（可能有几百G）数据，做一次完整备份时间很长，而且影响数据库服务器的性能，为保证数据安全，大多采用完整备份+事务日志备份来保证数据安全。例如：一天做一次或者2天做一次完整备份，几个小时内做一次日志备份。（当然可以用写一个job来实现）

2、如SQLserver2005里的镜像就是采用的这种事务日志同步的方法保证数据的同步。

3、如果恢复的日志数据出现”LSN“太早和太晚说明了事务日志间的不连续。这时要注意备份的时间和顺序。

八、附录：

I. 数据库的文件和备份的文件是两个不同的慨念.
每个数据库都至少由两个文件组成:
一个数据文件，默认扩展名的.mdf
一个日志文件，默认扩展名为.ldf
如果是用直接备份这两个数据文件来实现数据库备份，则备份的文件就是两个.
如果通过SQL的backupdatabase来备份，则SQL把这两个文件打包在一齐，所以只有一个文件.

II.    sqlserver提供了三种恢复模式，他们决定了有多少和什么样的数据可以被备份下来.
1.    简单恢复模式
主要应用于小型数据库和不经常改变的数据
2.    完整恢复模式
可以在最大范围内防止出现故障时丢失数据，它包括数据库备份和食物日志备份，并提供全面保护.这个模式使用数据库和所有日志的备份恢复数据库
3.    大容量日志恢复模式
在完整恢复模式下，所有大批量的数据导入导出操作产生的日志更加的少，他会进行日志的最小化记录，部分事务不会被记录下来.

III. 数据库备份可以分为4个备份类型。

1. 完全备份：创建备份完成时数据库内存在的数据的副本

2. 差异备份：只记录自上次数据库备份后发生更改的数据。差异数据库备份比数据库备份小，而且备份速度快，因此可以更经常地备份，经常备份将减少丢失数据的危险。

3. 日志备份：是自上次备份事务日志后对数据库执行的所有事务的一系列记录。可以使用事务日志备份将数据库恢复到特定的即时点（如输入多余数据前的那一点）或恢复到故障点。

4. 文件组备份：可以备份和还原数据库中的个别文件。可以只还原已损坏的文件，而不用还原数据库的其余部分，从而加快了恢复速度。

不同的备份类型适用的范围也不同。全备份，可以只用一步操作完成数据的全部备份，但执行时间比较长。差异备份和日志备份，都不能独立作为一个备份集来使用，需要进行一次全备份。文件备份必须与事务日志备份一起使用，所以文件备份只适用于完全恢复模型和大容量日志记录恢复模型。

每一种备份类型都有不足之处，要针对需要选择备份类型，或者使用几种备份方式的配合来完成数据库的备份。

经常使用备份方式组合有以下几种：

1. 全备份+差异备份：以一周为周期，星期日进行全备份，星期一到星期六每天进行差异备份。

2. 全备份+日志备份：以一周为周期，星期日进行全备份，星期一到星期六每天进行日志备份。

3. 文件组备份+日志备份：备份周期取决于数据库的大小和能力，每周期分别进行一部分数据文件备份，每天进行日志备份。

转自网络。