SQL Server2005数据恢复总结

55423

    前段时间9月24日由于自己的误操作误删除了SQL Server2005数据库一个表的数据...人生第一次误删数据！当时紧张恐慌到胃疼╮(╯▽╰)╭ ，这方面还小白，都不知道怎么办了，最终在同事的帮助下通过完整备份和事务日志备份恢复了，为了搞明白原理趁着想学的劲上网学习，根据自己的理解做了笔记和实验，供大家参考，有问题多多指教。


一．SQL Server常用备份策略1.完整备份+日志备份
2.完整备份+差异备份+日志备份

二．事务日志相关说明1. SQL Server 把事务日志文件划分为多个VLF（Virtual LogFile），即虚拟日志文件
2.虚拟日志文件是可以重新使用的空间的单位。只能截断仅包含不活动日志记录的虚拟日志文件。不能截断事务日志的活动部分（“活动日志”）
3.事务日志是一种回绕的文件。当创建数据库时，逻辑日志文件从物理日志文件的始端开始。新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。
数据库中的事务日志映射在一个或多个物理文件上。数据库引擎在内部将每一物理日志文件4.通过删除不活动的虚拟日志文件来释放逻辑日志中的空间。

三．数据库的三种恢复模式1.完整模式：能够利用事务日志让数据库恢复到出故障时间点或指定时间点
2.大容量日志：数据库日志不记录对数据修改的时间可以恢复到故障点 效率高但是无法恢复到指定时间点
3.简单：通过覆盖重复利用不活跃的逻辑日志文件  不能利用日志还原

注：当选用完整恢复模式时，如果从来没有对数据库进行完整备份，数据文件会增长但是事务日志文件不会增长，因为事务日志不知道起点，所以会通过覆盖重复利用已有的日志文件不会增长，如果执行过完整备份后，之后的日志都会保留，事务日志就会增长


四．创建备份设备名称1.命令行创建：
use master
go
exec sp_addumpdevice 'disk','备份设备名称','实际备份路径，例：e:\test.bak'

2.图形创建：
通过点击服务器对象然后点击备份设备来创建
backup database test to 备份设备名称
restore database test  from 备份设备名称

五．sql server备份常使用的相关命令尽管sql server图形操作也比较简单明了，但是命令有时候更快更方便
1.数据备份：
backup database test to disk='d:\test.bak'

2.事务日志备份：
backup log test to disk='d:\test.bak'

3.查看备份中的文件以及备份的类型等详细信息：
restore headeronly from  disk='d:\test.bak'

4.命令行使用完整和事务日志还原：
restore database test fromdisk='d:\test,bak' with file=1,replace,norecovery
restore log test from disk='d:\test,bak'with file=2,replace,norecovery
restore log test from disk='d:\test,bak'with file=3,recovery

5.还原指定时间点：
restore log test from disk='d:\test,bak'with file=3,recovery,stopat='2015-09-30 15:31:000'

6.参数含义:
file：表示要恢复数据文件的位置
replace：表示覆盖现有数据库
norecovery：表示还原后没有提交的事务不进行回滚，即还有可能要继续使用事务日志还原
Differential: 表示使用差异备份
init:表示进行覆盖已经有的备份文件
noinit:表示进行追加已经有的备份文件(默认)

7.截断事务日志命令：
backup log test with no_log
截断后可以手动收缩日志文件
截断并不减小物理日志文件的大小,减小日志文件的物理大小需要收缩文件。
事务日志备份会截断日志，释放了不活跃的VLF 增加了日志的可用空间
注：手动截断事务日志一定要谨慎！注意备份操作！

8.查看在线事务日志信息：
select * from SYSFILES

它的文件后缀名一般是.LDF

9.差异备份的命令：
backup database test to disk='d:\test.bak'with Differential
六．模拟数据误删除后使用完整和事务日志备份进行恢复1. --执首先要确认数据库恢复模式是：完整模式                           
2.--1号：执行完整备份（插入两条数据后）create table test (id init, name varchar(10))
insert into test values (1,'zhangsan')
insert into test values (2,'lisi')
3.--2号：事务日志备份（插入第三条数据后）insert into test values (3,'wangwu')
backup log test to disk='c:\backup\test.bak'
4.--3号：执行删除操作（插入第四条数据后）insert into test values (4,'zhaoliu')
drop table test
5.--如果在删除操作后直接在原有数据库进行还原操作会有相关错误提示如下：
6.--备份日志尾部use master
backup log test to disk='c:\backup\test2.bak'with norecovery
7.--查看备份的两个文件相关信息：





8.--使用图形进行完整还原:





9.--进行事务日志还原到2号:



10.--利用结尾日志还原到执行DROP操作前的时间点：


11.--查询恢复的数据
注：我这里用的是测试机来模拟的实验来说明原理，实际工作需要根据实际情况，最好不要直接在数据库上做恢复操作