备份与恢复
1. 备份和恢复的基础:事务
事务是SQL Server中的基本工作单元,事务处理保证了SQL Server数据库的一致性和可恢复性。
事务的ACID属性
A:原子性 不可分
C:一致性 数据完整
I:孤立性 执行了一半时的效果外界看不到,只能看到全部执行完的效果
D:持久性 执行成功后能持久保存到数据库
默认事务与显示事务
单条语句:update,delete,insert
显示事务:
begin transaction
commit transaction
rollback transaction
2.日志工作原理
a.应用程序修改数据
b.修改数据反应到buffer cache中
c.对数据的修改保存到磁盘的transaction log中,实时写,并且记录发生的时间
d.checkpoint发生时,把transaction log中的记录写到database中
因为transaction log是实时写,并且包含时间,所以可以使用它来回复数据库到任意的时间点。
系统重启时,SQL Server会恢复一致点,把事务日志刷新到数据文件。没有提交的事务回滚,已提交但为写到数据文件的就前滚。在每一个检查点上,保证事务日志与数据文件保持一致。
3.备份处理
a.联机备份,脱机备份
b.SQL Server恢复模型
简单恢复 只记录备份时刻的数据文件,不能恢复到即时点
完全回复 记录所有操作,但是日志文件特别大
大容量日志记录 节省了日志空间,但是不能恢复到即时点,不建议使用
可以在数据库【属性】->【选项】->【故障还原】中设置。注意:不要随意切换,应为三种回复模型的工作机制不一样,随意切换可能导致数据的不一致,所以切换时注意备份数据。
c.何时备份系统数据库
直接使用CREATE, ALTER ,DROP DATABASE语句修改数据库后需备份
master数据库:运行了某些系统存储过程:sp_addlogin这些修改服务器级数据的命令
msdb数据库:对自动化任务和DTS作了某些修改的命令
d.何时备份用户数据库
e.备份到何处
永久备份设备和临时备份文件
f.备份类型
1)完全数据库备份 Full Database Backup
备份整个数据文件和部分日志
2)差异数据库备份 Differential Backup
需要有完全数据库备份;备份自上次完全数据库备份后的数据修改部分和部分日志;
恢复时需要完全数据库备份和最近的差异数据库备份
3)事务日志备份
需要有完全数据库备份;备份自上次backup log语句到当前日志尾之间的所有数据库修改;截断事务日志
这样备份的数据量少,只备份上次备份到现在的修改;
恢复时需要完全数据库备份和所有的日志备份
事务日志备份—使用no_truncate选项
即便数据库不可访问,SQL Server也保留整个事务日志;对事务日志中已提交的事务不清除,不截断日志;使得数据库可以恢复到系统失效时刻;恢复数据库前的最后一条备份命令
BACKUP LOG Northwind TO Northwindbackup.Log with no_truncate
注意:如果日志文件损坏,此方法无效
事务日志备份—使用truncate_only或no_log选项
用于清理日志;
并不备份日志;
执行完该命令后可执行收缩文件命令截断日志文件;
执行完命令后应立刻执行完全数据库备份
建议使用:
组合使用:每周末做个完全备份,每天晚上做个差异备份,每天每隔3小时做日志备份
4)文件和文件组备份
用于超大型数据库
可单独备份数据文件
可备份文件组中所有文件
需要同时备份日志文件
MSDB数据库中的backupset表存放的是每次备份的详细信息
4.恢复数据库
a.恢复数据库的准备
1)校验备份
2)限制对数据库的访问:
只允许db_owner,dbcreator,sysadmin role成员访问
Single user访问
断开其余用户和数据库的连接
3)备份事务日志
确保数据库的一致性
捕获最后一次备份日志之后的数据库的修改
如数据库可访问,则使用不带参数的backup log
否则可尝试使用带no_truncate选项的backup log
b.恢复还是还原 RECOVERY or NORECOVERY