SQL Server 2005数据备份与还原

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
日志服务 SLS,月写入数据量 50GB 1个月
简介:
备份与还原概述: 数据库备份与还原: 备份是在数据库灾难发生时的最后一道防线-->"备份、备份、再备份"
                    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(如同要写入数据) 校验和(如果显示在媒体上) 检查目标设备上是否具有足够的空间
                     镜像备份媒体-->通过提供冗余来提高备份的可靠性 包含两个到四个镜像,每个镜像包含每个媒体簇的所有卷。每个镜像要求每个媒体簇都有单独的备份设备 通过将数据同步写入设备来保证镜像媒体具有相同的内容-->见下图:
                                    20032072
如何使用镜像备份媒体集呢?
20032073
  1.全选创建备份设备语句--按执行之后就创建一个叫做NwBackupDevice的备份设备了  2.全选修改数据库中的数据的语句--按执行之后就把Employees表里面的内容修改了  3.接下来就执行一个完全的数据库备份了  全选执行完整数据库备份的语句--按执行  注意: NW这个数据库其实就是NorthWind这个数据库  我把它从SQL Server 2000中拿出来,然后添加到SQL Server 2005里面 
20032074
  展开服务器对象--备份设备--可以看到已经创建了一个叫做NwBackupDevice的备份设备了  对着NwBackupDevice这个备份设备右键--选择属性--按媒体内容--可以看到有一个完整的数据库备份  1.全选使用COPY ONLY数据库仅复制数据库备份的语句--按执行  2.全选使用READ_URITE_FILEGROUPS选项进行部分备份的语句--按执行之后就只备份主文件组和读写的文件组  那么对于其它的只读文件组是不做备份的  展开数据库--对着NW这个数据库右键--选择属性--按文件组之后可以看到一共有4个文件组  其中有一个是主文件组
20032075
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企业版本支持 只能应用在完整或者大容量日志恢复模型 正在还原的部分不可用  默认情况下对文件和页的还原自动以在线还原形式执行
如何从数据库备份中还原数据呢?
20032076
1.全选将数据库还原到即时点的语句--按执行之后这个数据就被成功还原而且向它形成了一个指定的时间点  展开数据库--按F5键来刷新一下可以看到NW这个数据库的显示状态是正在还原  2.全选完成数据库恢复过程的语句--按执行之后--然后展开数据库--按F5键来刷新一下就可以看到NW这个数据库在线了  可以使用NW这个数据库了
20032077
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表是在另一个文件组 所以数据是不在线的
从数据库快照中恢复数据: 什么是数据库快照?  在一个时间点数据库一致状态的只读副本-->和源数据库必须同在一个服务器实例 为同一个数据库可以创建多个数据库快照 对于数据库的测试,报表创建和开发非常有用  是对数据库备份和还原的一种补充-->见下图:
                                        20032078
能从数据库快照中还原的数据(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'
如何使用数据库快照备份和还原数据呢?
20032079
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.全选在快照数据库中尝试更新的语句--按执行之后就会发现失败  为什么呢? 因为数据库快照里面的数据是只读的
20032080
3.全选检测数据库快照文件占用空间的大小,修改源数据中的数据的语句--按执行之后 然后打开C盘里面的SnapshotData这个文件夹--对着AdventureWorks_Data.mdf这个快照右键--选择属性--可以看到占用空间的大小比以前大了 这就说明了我们刚才对源数据库做修改的时候原来的数据就会被复制到快照数据库里面相应的数据文件中
5.全选验证修改,对比源数据库和快照数据库中的数据变化语句--按执行之后就可以看到源数据库表和快照数据库表的内容了
20032081
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
20032082
   全选将源数据库恢复到快照时的状态的语句--按执行之后这个数据库就会被恢复到快照生成的时候的状态了


本文转自 叶俊生 51CTO博客,原文链接:http://blog.51cto.com/yejunsheng/160958
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
6天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
数据库 Java 监控
Struts 2 日志管理化身神秘魔法师,洞察应用运行乾坤,演绎奇幻篇章!
【8月更文挑战第31天】在软件开发中,了解应用运行状况至关重要。日志管理作为 Struts 2 应用的关键组件,记录着每个动作和决策,如同监控摄像头,帮助我们迅速定位问题、分析性能和使用情况,为优化提供依据。Struts 2 支持多种日志框架(如 Log4j、Logback),便于配置日志级别、格式和输出位置。通过在 Action 类中添加日志记录,我们能在开发过程中获取详细信息,及时发现并解决问题。合理配置日志不仅有助于调试,还能分析用户行为,提升应用性能和稳定性。
38 0
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
67 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
47 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
230 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
148 1
|
3月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
165 3
|
3月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
下一篇
无影云桌面