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 restricte

2、d_user 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'

3、。1> create database mydb2> on primary3> (name='mydb_pri',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

4、> (name='mydb_fg1_2',filename='c:msdatamydb_fg1_2.ndf',9> size=5mb,maxsize=15mb,filegrowth=1mb10> log on11> (name='mydb_log',filename='c:msdatamydb_log.ldf',12> size=5mb,maxsize=15mb,filegrowth=1mb13> go - 创建数据库mydb供操作用CREATE DATABASE 进程正在磁盘'mydb

5、_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 modify filegroup mydb_fg1 default2> go - 更改数据库默认文件组为mydb_fg1文件组属性'DEFA

6、ULT' 已设置。1> select count(* from master.sysobjects2> go-1266(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 count(* from t12> g

7、o - 取系统表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('a'2> insert into t2 values('b'3> insert into t2 values('c'4> insert into t2 valu

8、es('d'5> insert into t2 values('e'6> go - 构造几条测试数据(1 行受到影响(1 行受到影响(1 行受到影响(1 行受到影响(1 行受到影响1> alter database mydb set recovery full2> go - 更改mydb数据库的恢复模式为full(默认恢复模式继承自model数据库至此,练习环境准备完毕。数据库mydb,两个文件组,3个数据文件一个日志文件。二、下面做数据库备份后模拟简单情况下的数据文件丢失的恢复备份:1、做mydb的全库备份:1> Backup d

9、atabase mydb to disk='c:bakmydb_all.bak' with format2> go已处理112 页,这些页属于数据库'mydb' 的文件'mydb_pri'(位于文件1 上。已处理32 页,这些页属于数据库'mydb' 的文件'mydb_fg1_1'(位于文件1 上。已处理32 页,这些页属于数据库'mydb' 的文件'mydb_fg1_2'(位于文件1 上。已处理1 页,这些页属于数据库'mydb' 的文件'mydb_l

10、og'(位于文件1 上。BACKUP DA TABASE 操作成功地处理了177 页,花费了0.412 秒(3.510 MB/秒。2、做mydb的差异备份1> set rowcount 5002> go1> delete from t12> go(500 行受到影响1> set rowcount 02> go1> delete from t2 where col1='c'2> go(1 行受到影响1> Backup database mydb to disk='c:bakmydb_dif.b01' w

11、ith format,differential2> go - 数据库差异备份(完全恢复模式下可选,差异备份不截断事务日志已处理32 页,这些页属于数据库'mydb' 的文件'mydb_pri'(位于文件1 上。已处理8 页,这些页属于数据库'mydb' 的文件'mydb_fg1_1'(位于文件1 上。已处理24 页,这些页属于数据库'mydb' 的文件'mydb_fg1_2'(位于文件1 上。已处理1 页,这些页属于数据库'mydb' 的文件'mydb_log'

12、(位于文件1 上。BACKUP DATABASE WITH DIFFERENTIAL 操作成功地处理了65 页,花费了0.364 秒(1.443MB/秒。3、做mydb的日志备份1> insert into t2 values ('x'2> go(1 行受到影响1> insert into t2 values ('y'2> go(1 行受到影响1> set rowcount 1002> go1> insert into t1 select * from master.sysobjects2> go(100 行受到影

13、响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/秒。1>1、模拟在服务器运行数据库关闭的状况下丢失一个次要文件时的恢复1> use master2> go1> select status from sysdatabases where

14、 name='mydb'2> go - 查sysdatabases表知mydb autoclose =onstatus-4194321(1 行受到影响1> alter database mydb set restricted_user with rollback immediate2> go - 限制用户访问正在回滚不合法事务。估计回滚已完成: 100%。1> select status from sysdatabases where name='mydb'2> go -数据库自动关闭(否则无法删除数据文件status-107793

15、8193 - 数据库已关闭(1 行受到影响- 在操作系统中删除mydb数据文件mydb_fg1_1.ndf,打开数据库报错1> use mydb2> go消息5105,级别16,状态5,服务器ANGEL,行 1设备激活错误。物理文件名'c:msdatamydb_fg1_1.ndf' 可能有误。消息945,级别14,状态2,服务器ANGEL,行 1由于文件不可访问,或者内存或磁盘空间不足,所以无法打开数据库'mydb'。详细信息请参阅SQL Server 错误日志。/* 说明:这种情况下的文件丢失等同于服务器关闭后丢失文件,一般出现机率不大还原步骤:A

16、、先还原数据库完全备份mydb_all.bak norecoveryB、还原数据库差异备份mydb_dif.b01(因差异备份不截断日志,可选,若要恢复至改差异备份的时间点则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

17、页,这些页属于数据库'mydb' 的文件'mydb_pri'(位于文件1 上。已处理32 页,这些页属于数据库'mydb' 的文件'mydb_fg1_1'(位于文件1 上。已处理32 页,这些页属于数据库'mydb' 的文件'mydb_fg1_2'(位于文件1 上。已处理1 页,这些页属于数据库'mydb' 的文件'mydb_log'(位于文件1 上。RESTORE DA TABASE 操作成功地处理了177 页,花费了0.267 秒(5.417 MB/秒。1>

18、 restore database mydb from disk='c:bakmydb_dif.b01' with norecovery2> go已处理32 页,这些页属于数据库'mydb' 的文件'mydb_pri'(位于文件1 上。已处理8 页,这些页属于数据库'mydb' 的文件'mydb_fg1_1'(位于文件1 上。已处理24 页,这些页属于数据库'mydb' 的文件'mydb_fg1_2'(位于文件1 上。已处理1 页,这些页属于数据库'mydb'

19、的文件'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/秒。- 恢复结果检查1> use mydb2>

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

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

22、='c:bakmydb_all.bak' with norecovery 2> restore log mydb from disk='c:bakmydb_log.001' with recovery 3> go 设备激活错误。物理文件名 'c:msdatamydb_log.ldf' 可能有误。 已处理 112 页，这些页属于数据库 'mydb' 的文件 'mydb_pri'（位于文件 1 上） 。 已处理 32 页，这些页属于数据库 'mydb' 的文件 'mydb_fg1_1

23、'（位于文件 1 上） 。 已处理 32 页，这些页属于数据库 'mydb' 的文件 'mydb_fg1_2'（位于文件 1 上） 。 已处理 1 页，这些页属于数据库 'mydb' 的文件 'mydb_log'（位于文件 1 上） 。 RESTORE DATABASE 操作成功地处理了 177 页，花费了 0.253 秒（5.716 MB/ 秒） 。 已处理 20 页，这些页属于数据库 'mydb' 的文件 'mydb_log'（位于文件 1 上） 。 RESTORE LOG 操作成功地处

24、理了 20 页，花费了 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 行受到影响） 总结：其实 MSsql server 的这种备份恢复模式的恢复能力很强，即使 drop 整个数据库，丢 总结 失所有数据库文件也能正常恢复，需要： a 数据库完整备份 b 数据库差异备份(可选 c 事务日志备份 恢复的效果由数据库的备份所决定，

25、能恢复至数据库完整备份时间点； 能恢复至数据库 差异备份时间点；能恢复至最后一次日志备份时的数据库(在线日志丢失的情况。 不同恢复模式下差异备份的作用总结： 不同恢复模式下差异备份的作用总结： 差异备份只备份数据库中 dcm 页指定的数据，dcm 页跟踪自上一次数据库做全库备份 以来数据库中发生变化的数据页(意味全库备份清空 dcm 页。 差异备份主要是为简单恢复模式服务，完整备份+差异备份的模式能使简单恢复模式的 数据库能恢复至差异备份时刻(差异备份触发 check point，故简单模式中差异备份造成日志 截断。 在 full 或 bulk_logged 模式下，差异备份就不是那么重要了。

26、在时间点 b 做差异备份可 允许删除去自数据库完全备份到时间点 b 的所有事务日志备份集(也包括前一个差异日志备 份。可以考虑为了后面两个目的来使用差异备份：1、节约备份空间 2、简化可能的恢复过 程。 差异备份与 DCM 页： 1、 做差异备份时 DCM 页是否重置？ 答：否！(通过完成两次差异备份，但只恢复后一次差异备份可以得出结论 2、 update table_a set c1=2 where c1=2 这条 update 语句是否被 dcm 页所跟 踪？ 答：否！(实际上该 update 并不执行，通过 dbcc log(db_name可以看到。 以上的总结详细的列出了备份与恢复时的具体命令， 让大家对数据库的备份与恢复的基 本步骤一个具体的认识。 三、 下面主要针对各种数据丢失的某些具体情况做出恢复方案 1、在数据库打开并有活动事务的情况下，媒体发生故障，恢复步骤： 在数据库打开并有活动事务的情况下，媒体发生故障，恢复步骤： 步骤 如果数据库的当前事务日志文件可用而且没有损坏，则可以将数据库还原到故障点 发生时的状态。若要将数据库还原到故障点： 1. 备份当前活动事务日志(backup log with no_truncate no