SQL Server学习笔记之四(什么是数据库的备份策略)

 

备份与恢复

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