SQL Server 2005数据备份与还原

最新推荐文章于 2022-12-13 14:42:35 发布

转载最新推荐文章于 2022-12-13 14:42:35 发布 · 273 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/430320

文章标签：

#数据库

本文详细介绍了SQL Server 2005的备份与恢复机制，包括不同类型的备份（如完整、差异、事务日志备份）、备份的增强功能、各种恢复模式的特点，以及如何进行即时点恢复、文件和文件组的恢复等高级操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

备份与还原概述: 数据库备份与还原: 备份是在数据库灾难发生时的最后一道防线-->"备份、备份、再备份"
SQL Server 2005备份与还原增强特性提高了系统的可用性-->部分备份、备份镜像... 在线还原、分段还原、页面级还原
数据库的恢复模式: 数据库的恢复模式控制备份和还原的行为三种恢复模式: 简单恢复模式-->事务日志被自动截断,不能使用日志文件进行恢复完整恢复模式-->保留所有操作的完整事务日志大容量日志恢复模式-->简要记录大容量操作(索引创建和大容量加载)的日志,完整记录其它事务的日志

数据库恢复模式的选择-->见下列表:

还原操作优点数据丢失情况恢复到即时点

简单恢复模型高性能大容量复制操作,最小的日志空间占用数据库备份后所做的更改丢失只能恢复到备份时刻

大容量日志高性能大容量复制操作,较小的日志空间占用会丢失备份后大容量操作的数据可以恢复到任意备份的结尾

完全最小的数据丢失可能,恢复到即时点日志不损坏将不丢失任何数据恢复到任意时间点

数据库的恢复模式和所支持的还原操作-->见下列表:

还原操作完整还原模式大容量日志还原模式简单还原模式
数据还原日志可用时完整还原某些数据丢失上次完整或者差异备份后的数据丢失

即时点还原日志所包含的任何时间日志中包含大容量操作时不允许不支持

文件级还原完全支持不完全支持仅对.ndf辅助文件可用

页面级还原完全支持不完全支持无

文件组还原完全支持不完全支持仅对.ndf辅助文件可用

创建数据库备份: 数据库备份-->完整数据库备份: 包含数据库中的所有数据为差异、事务日志备份创建基准数据库差异数据库备份: 差异备份比基准备份小而且备份速度快,便于经常性的备份任务新特性: 不同于SQL Server的前期版本,在SQL Server 2005中,所有数据和差异备份均包含日志记录

部分备份和部分差异备份-->部分备份: 包含主文件组、每个读写文件组及任何指定只读文件中的所有数据只读数据库的部分备份仅包含主文件组 READ_WRITE_FILEGROUPS 应用场景: 在最后一次完整备份后,拥有一个或多个只读文件组的情况
[BACKUP DATABASE Northwind READ_WRITE_FILEGROUPS TO DISK='C:\NWBackup.BAK'

                    文件和文件组备份-->针对大型数据库,可以分别备份和还原数据库中的文件: 仅还原已损坏的文件,而不必还原数据库的其余部分文件差异备份: 不要对同一数据库进行数据库差异备份和文件差异备份简单还原模式的限制: 只能对只读文件和文件组进行备份   增加了备份和还原管理的复杂性
                    事务日志备份-->通过事务日志备份可以将数据库恢复到即时点必须至少有一个完整数据库备份基础尾日志备份: 在恢复数据库前保存尚未备份的日志数据 SQL Server 2005要求先备份日志尾部,然后再还原当前的数据库使用选项NORECOVERY或NO_TRUNCATE
                     使用COPY_ONLY备份选项-->在不打断正常备份系列的情况下复制数据库内容: 不截断事务日志,不包含在要进行还原的日志系列中该备份将不依赖于任何其它备份
                    [BACKUP DATABASE AdventureWorks TO AWBackup WITH COPY_ONLY]
                    确保备份完整性的选项-->在BACKUP和RESTORE中使用带有CHECKSUMS选项: 验证页校验和备份时生成整个备份的校验和使用RESTORE VERIFYONLY: 备份集是否完整以及所有卷是否可读页ID(如同要写入数据) 校验和(如果显示在媒体上) 检查目标设备上是否具有足够的空间

镜像备份媒体-->通过提供冗余来提高备份的可靠性包含两个到四个镜像,每个镜像包含每个媒体簇的所有卷。每个镜像要求每个媒体簇都有单独的备份设备通过将数据同步写入设备来保证镜像媒体具有相同的内容-->见下图:

如何使用镜像备份媒体集呢?

1.全选创建备份设备语句--按执行之后就创建一个叫做NwBackupDevice的备份设备了 2.全选修改数据库中的数据的语句--按执行之后就把Employees表里面的内容修改了 3.接下来就执行一个完全的数据库备份了全选执行完整数据库备份的语句--按执行注意: NW这个数据库其实就是NorthWind这个数据库我把它从SQL Server 2000中拿出来,然后添加到SQL Server 2005里面

展开服务器对象--备份设备--可以看到已经创建了一个叫做NwBackupDevice的备份设备了对着NwBackupDevice这个备份设备右键--选择属性--按媒体内容--可以看到有一个完整的数据库备份 1.全选使用COPY ONLY数据库仅复制数据库备份的语句--按执行 2.全选使用READ_URITE_FILEGROUPS选项进行部分备份的语句--按执行之后就只备份主文件组和读写的文件组那么对于其它的只读文件组是不做备份的展开数据库--对着NW这个数据库右键--选择属性--按文件组之后可以看到一共有4个文件组其中有一个是主文件组

USE master -->全选这条语句--按执行之后就得到9个介质了其实是三个媒体集在C盘DBBackup这个文件夹里面可以看到9个备份介质
Go
BACKUP DATABASE Northwind
To DISK = 'C:\DBBackup\A-1.BAK',DISK = 'C:\DBBackup\A-2.BAK',DISK = 'C:\DBBackup\A-3.BAK'
MIRROR TO DISK = 'C:\DBBackup\B-1.BAK',DISK = 'C:\DBBackup\B-2.BAK',DISK = 'C:\DBBackup\B-3.BAK'
MIRROR TO DISK = 'C:\DBBackup\C-1.BAK',DISK = 'C:\DBBackup\C-2.BAK',DISK = 'C:\DBBackup\C-3.BAK'
WITH FORMAT,MEDIANAME = 'AdventureWorksSet1'
Go

如果三个镜像媒体集中目前只有A-1、B-2、C-3这三个媒体集可用的话此时怎么办呢? 我们可以使用来自不同镜像集中媒体来还原数据库
USE master -->全选这条语句--按执行之后来自不同媒体集的文件就成功进行还原了
Go
RESTORE DATABASE Northwind
FROM DISK = 'C:\DBBackup\C-3.BAK', DISK = 'C:\DBBackup\B-2.BAK', DISK = 'C:\DBBackup\A-1.BAK'
WITH FILE = 1, REPLACE
Go
从备份中还原数据库: 数据库还原过程原理-->数据库还原阶段的过程: 数据库复制阶段-->从数据库的备份媒体将所有数据、日志和索引页复制到数据库文件中重做阶段(Redo)/前滚(Roll Forward)-->将记录的事务应用到从备份复制的数据,以将这些数据前滚到恢复点撤销阶段(Undo)/回滚(Roll Back)-->回滚所有未提交的事务并使用户可以使用此数据库,回滚阶段后将无法还原后续的备份
                          数据库还原选项-->RECOVERY选项: 完成重做和撤消两个阶段数据库处于一致状态,完成后数据库在线   NORECOVERY选项: 不执行撤消阶段以保留未提交的事务允许还原其他备份以将数据库进一步前滚
                          即时点还原-->支持数据库备份的即时点还原: 在SQL Server 2005之前的版本中只支持事务日志的及时点还原使用STOPAT选项指明时间点
[RESTORE DATABASE AdventureWorks FROM AWBackup WITH RECOVERY ,STOPAT='Mar 1,2006 2:00 PM'                     执行段落还原-->通过段落还原分阶段逐步恢复由多个文件组组成的数据库: 未恢复文件组被标记为离线,其中的数据不可访问还原步骤: 还原主文件组和需要立即使用的辅助文件组在以后通过逐步还原其它辅助文件组
                           [RESTORE DATABASE AWTemp FILE='AdventureWorks_data_1' FROM AWBackup

WITH PARTIAL,MOVE'AdventureWorks_data_1'TO'AWTemp_data_1.mdf']

                          执行页面还原-->通过执行页面还原对数据库中损坏的页进行修复获取页面损坏信息: SQL Server错误日志 msdb.dbo.suspect_pages表中
                                                                                                 [RESTORE DATABASE AdventureWorks
                                                                                                  PAGE='AdventureWorks_data_1:832'
                                                                                                  FROM AWBackup]
                          数据库的在线还原-->数据库在进行部分还原时仍处于在线可用状态限制条件:只有SQL Server 2005企业版本支持只能应用在完整或者大容量日志恢复模型正在还原的部分不可用默认情况下对文件和页的还原自动以在线还原形式执行

如何从数据库备份中还原数据呢?

1.全选将数据库还原到即时点的语句--按执行之后这个数据就被成功还原而且向它形成了一个指定的时间点展开数据库--按F5键来刷新一下可以看到NW这个数据库的显示状态是正在还原 2.全选完成数据库恢复过程的语句--按执行之后--然后展开数据库--按F5键来刷新一下就可以看到NW这个数据库在线了可以使用NW这个数据库了

USE master -->全选这条语句--按执行之后就生成了一个叫做NwTmp的数据库了这个数据库包含了主文件组和ReadWriteFG文件组
Go
RESTORE DATABASE NwTmp FILEGROUP='ReadWriteFG'
FROM NwBackupDevice
WITH FILE =3,
MOVE N'Northwind'To N'C:\tmp\NW.mdf',
MOVE N'ReadWriteFile' TO N'C:\tmp\ReadWriteFile.ndf',
MOVE N'Northwind_log' TO N'C:\tmp\NW.ldf',
NOUNLOAD, STATS = 10,REPLACE
Go
2.全选查询Customers表的语句--按执行之后就可以看到表的内容了
3.全选查询Employees表的语句--按执行之后就看到错误了为什么错误呢? 因为我们只是分段地还原了ReadWriteFG这样一个文件组而Employees表是在另一个文件组所以数据是不在线的
从数据库快照中恢复数据: 什么是数据库快照? 在一个时间点数据库一致状态的只读副本-->和源数据库必须同在一个服务器实例为同一个数据库可以创建多个数据库快照对于数据库的测试,报表创建和开发非常有用是对数据库备份和还原的一种补充-->见下图:

能从数据库快照中还原的数据(1)-->恢复被更新的数据
                                                        [UPDATE HumanResources Department
                                                         SET Name=
                                                         (
                                                              SELECT Name
                                                              FROM AdventureWorks_dbsnapshot_1800 HumanResource Department
                                                              WHERE DepartmentID=1
                                                         )
                                                         WHERE DepartmentID=1]
                         能从数据库快照中还原的数据(2)-->恢复被删除的行
                                                         [ALTER TABLE Production WorkOrderRouting
                                                          NOCHECK CONSTRAINT CK_WorkOrderRouting_ActualEndDate

INSERT INTO Production WorkOrderRouting
SELECT * FROM AdventureWorks_dbsnapshot_1800 Production WorkOrderRouting

                                                          ALTER TABLE Production WorkOrderRouting
                                                          CHECK CONSTRAINT CK_WorkOrderRouting_ActualEndDate
                          能从数据库快照中还原的数据(3)-->恢复被删除的对象: 由快照数据库中对象生成脚本在当前数据库中运行脚本恢复删除的对象
                          能从数据库快照中还原的数据(4)-->将数据库恢复到快照时的状态
                                                                    [RESTORE DATABASE AdventureWorks
                                                                    FROM DATABASE_SNAPSHOT='AdventureWorks_dbsnapshot_1800'

如何使用数据库快照备份和还原数据呢?

USE Adventureworks -->全选这条语句--按执行之后--打开C盘里面的SnapshotData这个文件夹之后就可以看到已经成功创建了一个叫做AdventureWorks_Data.mdf的快照了
GO
CREATE DATABASE AdventureWorks_dbsnapshot_1800
ON (NAME = AdventureWorks_Data, FILENAME = 'C:\SnapshotData\AdventureWorks_Data.mdf')
AS SNAPSHOT OF AdventureWorks
Go
1.全选查询快照数据库的语句--按执行之后就可以看到数据里面的内容了其实跟AdventureWorks这个数据库里面的内容是完全一样的
2.全选在快照数据库中尝试更新的语句--按执行之后就会发现失败为什么呢? 因为数据库快照里面的数据是只读的

3.全选检测数据库快照文件占用空间的大小,修改源数据中的数据的语句--按执行之后然后打开C盘里面的SnapshotData这个文件夹--对着AdventureWorks_Data.mdf这个快照右键--选择属性--可以看到占用空间的大小比以前大了这就说明了我们刚才对源数据库做修改的时候原来的数据就会被复制到快照数据库里面相应的数据文件中
5.全选验证修改,对比源数据库和快照数据库中的数据变化语句--按执行之后就可以看到源数据库表和快照数据库表的内容了

1.全选删除源数据库表Production.WorkOrderRouting中的行的语句--按执行之后就删除源数据库表Production.WorkOrderRouting中的行了
2.USE AdventureWorks -->全选恢复删除的数据行这条语句--按执行之后就把刚才误删除的那些数据还原回去
Go
ALTER TABLE Production.WorkOrderRouting
NOCHECK CONSTRAINT CK_WorkOrderRouting_ActualEndDate
Go
INSERT INTO Production.WorkOrderRouting
SELECT * FROM AdventureWorks_dbsnapshot_1800.Production.WorkOrderRouting
ALTER TABLE Production.WorksOrderRouting
CHECK CONSTRAINT CK_WorkOrderRouting_ActualEndDate