sqlserver备份与恢复学习笔记

1、Sql Server DatabaseBackup and Restore Practice Notes 温和该文档为本人在 MSsql server 数据库恢复与备份的学习过程中 ,结合具体操作 实践的一些总结 ,仅供参考。一、创建练习使用的数据库为 mydb,创建过程如下 :1 drop database mydb2 go -drop掉原使用的 mydb数据库 ,因有其他会话使用 ,数秒后报错退出消息 3702,级别 16,状态 4,服务器 ANGEL, 行 1无法除去数据库 mydb,因为它当前正在使用。1 alter database mydb set restricted_user

2、with rollback immediate2 go - 更改数据库为单用户使用模式 ,并强制其他会话回滚正在回滚不合法事务。估计回滚已完成 : 100%。1 drop database mydb2 go - 独占式访问数据库 ,可执行删除正在删除数据库文件 c:msdatamydb_fg1_2.ndf。正在删除数据库文件 c:msdatamydb_fg1_1.ndf。正在删除数据库文件 c:msdatamydb_log.ldf。正在删除数据库文件 c:msdatamydb_pri.mdf。1 create database mydb2 on primary3 (name=mydb_pri,

3、filename=c:msdatamydb_pri.mdf,4 size=5mb,maxsize=15mb,filegrowth=1mb,5 filegroup mydb_fg16 (name=mydb_fg1_1,filename=c:msdatamydb_fg1_1.ndf,7 size=5mb,maxsize=15mb,filegrowth=1mb,8 (name=mydb_fg1_2,filename=c:msdatamydb_fg1_2.ndf,9 size=5mb,maxsize=15mb,filegrowth=1mb10 log on11 (name=mydb_log,filen

4、ame=c:msdatamydb_log.ldf,12 size=5mb,maxsize=15mb,filegrowth=1mb13 go - 创建数据库 mydb供操作用CREATE DATABASE进程正在磁盘 mydb_pri 上分配 5.00 MB 的空间。CREATE DATABASE进程正在磁盘 mydb_fg1_1 上分配 5.00 MB 的空间。CREATE DATABASE进程正在磁盘 mydb_fg1_2 上分配 5.00 MB 的空间。CREATE DATABASE进程正在磁盘 mydb_log 上分配 5.00 MB 的空间。1 alter database mydb

5、modify filegroup mydb_fg1 default2 go - 更改数据库默认文件组为 mydb_fg1文件组属性 DEFAULT 已设置1 select count(* from master.sysobjects2 go1266(1 行受到影响1 use mydb2 go1 set rowcount 10002 go1 select * into t1 from master.sysobjects2 go(1000 行受到影响1 insert into t1 select * from master.sysobjects2 go(1000 行受到影响1 select cou

6、nt(* from t12 go - 取系统表 sysobjects的记录做数据源生成表 t1,t1 表在 mydb_fg1 上2000(1 行受到影响1 create table t2(col1 char(8000 on primary2 go - 在primary 文件组上建表 t2,char(8000意味着一行的数据将占用一个 page1 insert into t2 values(a2 insert into t2 values(b3 insert into t2 values(c4 insert into t2 values(d5 insert into t2 values(e6 g

7、o - 构造几条测试数据(1 行受到影响(1 行受到影响(1 行受到影响(1 行受到影响(1 行受到影响1 alter database mydb set recovery full2 go - 更改 mydb 数据库的恢复模式为 full( 默认恢复模式继承自 model 数据至此 ,练习环境准备完毕。数据库 mydb,两个文件组 ,3 个数据文件一个日志文 件。二、下面做数据库备份后模拟简单情况下的数据文件丢失的恢复备份:1、做 mydb 的全库备份 :1 Backup database mydb to disk=c:bakmydb_all.bak with format2 go已处理 1

8、12 页,这些页属于数据库 mydb 的文件 mydb_pri（ 位于文件 1 上。已处理 32 页,这些页属于数据库 mydb 的文件mydb_fg1_1（位于文件 1 上。已处理 32 页,这些页属于数据库 mydb 的文件mydb_fg1_2（位于文件 1 上。已处理 1 页,这些页属于数据库 mydb 的文件mydb_log（位于文件 1 上。BACKUP DA TABASE 操作成功地处理了 177 页,花费了 0.412 秒（3.510 MB/ 秒。2、做 mydb 的差异备份1 set rowcount 5002 go1 delete from t12 go（500 行受到影响1

9、 set rowcount 0 2 go1 delete from t2 where col1=c2 go（1 行受到影响1 Backup database mydb to disk=c:bakmydb_dif.b01 with format,differential2 go - 数据库差异备份 （完全恢复模式下可选 ,差异备份不截断事务日志 已处理 32 页,这些页属于数据库 mydb 的文件 mydb_pri（位于文件 1 上。 已处理 8 页,这些页属于数据库 mydb 的文件mydb_fg1_1（位于文件 1 上。 已处理 24 页,这些页属于数据库 mydb 的文件mydb_fg1_

10、2（位于文件 1 上。 已处理 1 页,这些页属于数据库 mydb 的文件mydb_log（位于文件 1 上。 BACKUP DATABASE WITH DIFFERENTIAL 操作成功地处理了 65 页,花费了 0.364 秒（1.443 MB/秒。3、做 mydb 的日志备份1 insert into t2 values （x2 go（1 行受到影响1 insert into t2 values (y2 go(1 行受到影响1 set rowcount 1002 go1 insert into t1 select * from master.sysobjects2 go(100 行受到影

11、响1 backup log mydb to disk=c:bakmydb_log.001 with format2 go - 数据库事务日志备份已处理 20 页,这些页属于数据库 mydb 的文件mydb_log(位于文件 1 上 BACKUP LOG 操作成功地处理了 20 页,花费了 0.190 秒(0.829 MB/秒。 11、模拟在服务器运行数据库关闭的状况下丢失一个次要文件时的恢复1 use master2 go1 select status from sysdatabases where name=mydb2 go - 查 sysdatabases表知 mydb autoclose

12、 =on status4194321(1 行受到影响1 alter database mydb set restricted_user with rollback immediate2 go - 限制用户访问正在回滚不合法事务。估计回滚已完成 : 100%。1 select status from sysdatabases where name=mydb2 go -数据库自动关闭 (否则无法删除数据文件status1077938193 - 数据库已关闭(1 行受到影响- 在操作系统中删除 mydb 数据文件 mydb_fg1_1.ndf,打开数据库报错1 use mydb2 go消息 5105

13、,级别 16,状态 5,服务器 ANGEL, 行 1设备激活错误。物理文件名 c:msdatamydb_fg1_1.ndf 可能有误。消息 945,级别 14,状态 2,服务器 ANGEL,行 1由于文件不可访问 ,或者内存或磁盘空间不足 ,所以无法打开数据库 mydb。详细信息请参阅 SQL Server 错误日志/* 说明 :这种情况下的文件丢失等同于服务器关闭后丢失文件 ,一般出现机率不 大还原步骤 :A 、先还原数据库完全备份 mydb_all.bak norecoveryB、还原数据库差异备份 mydb_dif.b01（因差异备份不截断日志 ,可选 ,若要恢复 至改差异备份的时间点则

14、 recoveryC、还原事务日志备份 mydb_log.001 recovery1 restore database mydb from disk=c:bakmydb_all.bak with norecovery2 go消息 5105,级别 16,状态 5,服务器 ANGEL, 行 1设备激活错误。物理文件名 c:msdatamydb_fg1_1.ndf 可能有误。已处理 112 页,这些页属于数据库 mydb 的文件mydb_pri（位于文件 1 上。已处 理 32 页,这些页属于数据库 mydb 的文件 mydb_fg1_1（位于文件 1 上。已处理 32 页, 这些页属于数据库 my

15、db 的文件 mydb_fg1_2（位于文件 1 上。已处理 1 页,这些页属 于数据库 mydb 的文件 mydb_log（位于文件 1 上。 RESTORE DA TABASE 操作成 功地处理了 177 页,花费了 0.267 秒（5.417 MB/秒。1 restore database mydb from disk=c:bakmydb_dif.b01 with norecovery2 go已处理 32 页,这些页属于数据库 mydb 的文件mydb_pri（位于文件 1 上。已处 理 8 页,这些页属于数据库 mydb 的文件mydb_fg1_1（位于文件 1 上。已处理 24 页,

16、 这些页属于数据库 mydb 的文件 mydb_fg1_2（位于文件 1 上。已处理 1 页,这些页属于数据库 mydb 的文件mydb_log（位于文件 1 上。RESTORE DA TABASE 操作成功地处理了 65 页,花费了 0.238 秒（2.207 MB/秒。1 restore log mydb from disk=c:bakmydb_log.001 with recovery2 go已处理 20 页,这些页属于数据库 mydb 的文件mydb_log（位于文件 1 上 RESTORE LOG 操作成功地处理了 20 页,花费了 0.046 秒（3.428 MB/秒 - 恢复结果

17、检查1 use mydb2 go1 select count（* from t12 go - 记录数 2000-500+100=16001600(1 行受到影响1 select convert(varchar(10,col1 from t22 go - 数据正确 ,但该表数据与丢失的数据文件无关y(6 行受到影响2、模拟在服务器运行数据库关闭的状况下丢失主要文件时的恢复恢复步骤与以上丢失次要文件时的相同。3、模拟在服务器运行数据库关闭的状况下丢失日志文件时的恢复1 use mydb2 go1 insert into t2 values(car2 go(1 行受到影响1 insert into

18、t2 values(plane2 go(1 行受到影响- 关闭数据库删除在线日志文件1 use mydb2 go消息 945,级别 14,状态 2,服务器 ANGEL,行 1由于文件不可访问 ,或者内存或磁盘空间不足 ,所以无法打开数据库 mydb。详 细信息请参阅 SQL Server 错误日志。 设备激活错误。物理文件名c:msdatamydb_log.ldf 可能有误。 1 restore database mydb fromdisk=c:bakmydb_all.bak with norecovery 2 restore log mydb from disk=c:bakmydb_log.

19、001 with recovery 3 go 设备激活错误。物理文件名 c:msdatamydb_log.ldf 可能有误 件 mydb_pri（位于文件 1 上） mydb_fg1_1（位于文件 1 上） mydb_fg1_2（位于文件 1 上） mydb_log（位于文件 1 上）已处理 112 页，这些页属于数据库 mydb 的文 已处理 32 页，这些页属于数据库 mydb 的文件 已处理 32 页，这些页属于数据库 mydb 的文件 已处理 1 页，这些页属于数据库 mydb 的文件RESTORE DATABASE 操作成功地处理了177mydb20 页，。 RESTORE LOG

20、操作成功地处理了页，花费了 0.253 秒（ 5.716 MB/ 秒） 。已处理 20 页，这些页属于数据库 的文件 mydb_log（位于文件 1 上） 花费了 0.046 秒（ 3.428 MB/秒） 。 1 use mydb 2 go 1 select convert（varchar（10,col1 from t2 2 go - 查看 t2 表，丢失自上次日志备份以来的事务 （插入的 car、plane 记录 a b x d e y （6 行受到影响） 总结：其实 MSsqlserver 的这种备份恢复模式的恢复能力很强，即使 drop 整个数据库，丢 总结 失所 有数据库文件也能正常恢

21、复，需要： a 数据库完整备份 b 数据库差异备份 （可选 c 事务日志备份 恢复的效果由数据库的备份所决定， 能恢复至数据库完整备份时间 点； 能恢复至数据库 差异备份时间点；能恢复至最后一次日志备份时的数据库 （在 线日志丢失的情况。 不同恢复模式下差异备份的作用总结： 不同恢复模式下差异 备份的作用总结： 差异备份只备份数据库中 dcm 页指定的数据， dcm 页跟踪自上 一次数据库做全库备份 以来数据库中发生变化的数据页 （意味全库备份清空 dcm 页。 差异备份主要是为简单恢复模式服务，完整备份 +差异备份的模式能使简单恢 复模式的 数据库能恢复至差异备份时刻 （差异备份触发 che

22、ck point，故简单模式中 差异备份造成日志 截断。 在 full 或 bulk_logged 模式下，差异备份就不是那么重要 了。在时间点 b 做差异备份可 允许删除去自数据库完全备份到时间点 b 的所有事 务日志备份集 （也包括前一个差异日志备 份。可以考虑为了后面两个目的来使用差 异备份： 1、节约备份空间 2、简化可能的恢复过程。 差异备份与 DCM 页： 1、 做差异备份时 DCM 页是否重置？ 答：否！ （通过完成两次差异备份，但只恢复后一次差异备份可以得出结论2、 updatetable_a set c1=2 where c1=2这 条 update 语句是否被 dcm 页所

23、跟 踪？ 答：否！ （实际 上该 update 并不执行，通过 dbcc log（db_name可以看到。 以上的总结详细的列出 了备份与恢复时的具体命令， 让大家对数据库的备份与恢复的基 本步骤一个具体 的认识。 三、 下面主要针对各种数据丢失的某些具体情况做出恢复方案 1、在数 据库打开并有活动事务的情况下，媒体发生故障，恢复步骤： 在数据库打开并有 活动事务的情况下，媒体发生故障，恢复步骤： 步骤 如果数据库的当前事务日志 文件可用而且没有损坏，则可以将数据库还原到故障点 发生时的状态。若要将数 据库还原到故障点： 1. 备份当前活动事务日志 （backup log with no_tr

24、uncate no_truncate。 2. 还原最新的数据库备份但不恢复数据库 （restore with norecovery norecovery。 3. 如果有差异备份，则还原最新的那个备份。 4. 按照创建时的相同顺 序， 还原自数据库备份或差异备份后创建的每个事务日志备份， 但不恢复数据 库。 5. 应用最新的日志备份（在步骤 1 中创建的）并恢复数据库。 说明： 说明： 操作成功。步骤 1 的备份保证了在前一次日志备份点到故障发生点的事务记录得 以备份。活动事务在数据库恢复时回滚 （restore 操作要求独占式访问数据库， alter database restricted_u

25、se时r 强制回滚事务 .。 2、丢失 master, msdb, model, distribution 等系统数据库数据文件时的恢复步骤 、 Master 数据库丢失将使服务器无法启动， 此时任何的恢复操作都将无法进行。 Msdb 数据库存放作业、报警、代理程序调度 等信息，如无备份则要手工创建。 Model 数据库，如有做修改，则无备份情况下 需手工还原更改。 Distribution 数据库存放复制信息，由系统进程自动创建并维 护，如无备份则需重新定 义复制。 丢失 master 等系统数据库后，服务器无法启 动。恢复步骤如下： 1. 使用重建工具 rebuildm 命令启动系统数据库重建向导 （需要 有 sqlserver 软件安装 需要有 盘 2. 如 master 数据库有备份时： a Sqlservr c m - 单用户模式启动 b Restore database master 还原. master 数据库 （系统信息不丢 失： 账户信 息，数据文件位置、系统配置等 c 如有备份，且用户数据库数据文件 完好无损，则还原 master 后用户数据库可 用（因 master 数据库还原后存有用户数 据库信息，如文件位置等） d 还原其他系统数据库的备份： msdb, dist