009_备份
009_备份
--------------------------有四种备份方式-----------------------
--****************************************************
--1.完整数据库备份
--用BACKUP DATABASE命令备份
--例:BACKUP DATABASE教学管理TO W1 WITH INIT
--eg:
--完整数据库备份设备
exec sp_addumpdevice @devtype=N'disk',@logicalname=N'copy_test_back_data',@physicalname=N'E:\copy_test_back_data.bak'
--备份整个数据库
backup database test_back to copy_test_back with init
--注:{ NOINIT | INIT }
--控制备份操作是追加到还是覆盖备份介质中的现有备份集。默认为追加到介质中最新的备份集 (NOINIT)。
--****************************************************
--2.差异备份
--用BACKUP DATABASE命令备份
--例: BACKUP DATABASE 教学管理 TO W1 WITH DIFFERENTIAL
--eg:
--定义数据库备份设备
exec sp_addumpdevice @devtype=N'disk',@logicalname=N'copy_test_back_data',@physicalname=N'D:\copy_test_back_data.bak'
--备份整个数据库
backup database test_back to copy_test_back with init
--备份差异数据
backup database test_back to copy_test_back with differential
--****************************************************
--3.日志备份
--用BACKUP LOG命令备份
--例: BACKUP LOG 教学管理 TO L1 WITH INIT
--eg:
--****************************************************
--4.文件/文件组备份
--用BACKUP DATABASE命令备份
例: sp_addumpdevice 'disk','WF','E:\SQLBF\BACKF.DAT'
BACKUP DATABASE 教学管理
FILE=’ARCHDAT1’,FILE=’ARCHDAT2’
TO WF
BACKUP DATABASE 教学管理
FILEGROUP=’PRIMARY’
TO WF
--eg:
--创建备份设备---------------------------
--数据库备份设备
exec sp_addumpdevice @devtype=N'disk',@logicalname=N'copy_test_back_data',@physicalname=N'E:\copy_test_back_data.bak'
--日志备份设备
exec sp_addumpdevice @devtype=N'disk',@logicalname=N'copy_test_back_log',@physicalname=N'copy_test_back_log.bak'
--文件备份设备
--备份整个数据库
backup database test_back to copy_test_back with init
--差异备份
backup database test_back to copy_test_back with diffrential
--日志备份
backup log test_back to copy_test_back with init
--文件/文件组备份
backup database test_back FILE=N'file1',FILE=N'file2' to cppy_test_back
backup database test_back FILEGROUP=N'file_' to copy_test_back
--------------------------有三种备份模型-----------------------
--简单恢复模式下的备份
--在完整恢复模式下备份
--在大容量日志恢复模式下备份
/*备份模型所需条件
数据库完整 数据库差异 事务日志 文件或文件组
完全 必需（或文件备份） 可选 必需 可选
大容量日志 必需（或文件备份） 可选 必需 可选
简单 必需 可选 不允许 不允许
*/
在SQL Server对于日志文件的管理，是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).
SQL Server创建数据库时，根据日志文件(ldf)的大小，生成VLF的数量公式如下。【1M-64M 4】【65M-1GB 8】【>1GB 16】
同时，对于日志文件的增长，SQL Server使用了和创建数据库时相同的公式，也就是每次增长比如为2M，则按照公式每次增长4个VLFs.
由此可以看出，指定合适的日志文件初始大小和增长，是减少日志碎片最关键的部分.
--查看虚拟日志文件相关信息
DBCC loginfo(test)
当针对数据库对象所做的任何修改保存到数据库之前，相应的日志首先会被记录到日志文件。这个记录会被按照先后顺序记录到日志文件的逻辑末尾，并分配一个全局唯一的日志序列号(log sequence number,简称LSN)
为了保障事物的原子性和持久性，SQL SERVER 采用wal（WRITE-Ahead logging）预写式日志技术来实现。核心思想：数据写入磁盘之前，先写入日志。
1、缓冲区写入begin TRAN
2、缓冲区日志页写入要修改的数据
3、缓冲区写入要修改的数据到数据页(被修改的数据页被称为'脏'页)
4、缓冲区写入commit
5、将数据写入日志文件
6、发送确认信息(ACK)到客户端(SMSS,ODBC等）
完成上述操作后，只是将日志写入日志文件了，缓冲区的数据其实还没有写入到磁盘，还需要经过checkpoint或lazy write这个步骤后，才会将数据写入磁盘
CHECKPOINT(检查点) 和 lazy write 的相同点和区别
相同点：两者都是将缓冲区的脏页写入到磁盘
不同点：checkpoint可通过checkpoint 60（60秒刷新一次）这个t-sql来修改其刷新频率。
checkpoint执行时，无论事物是否commit，都会将缓冲区的'脏'页写入磁盘，之后会将未commit的'脏'页回滚。
除了自动CheckPoint之外，CheckPoint还会发生在Alter DataBase以及关闭SQL Server服务器时。
lazy write是在内存达到一定临界时，将已缓冲区的'脏'页写入到磁盘，将未修改的页释放并回收资源。
通常情况下，将'脏'页写入磁盘的工作，Lazy Writer要做的比CheckPoint会多出许多。
通过指定CheckPoint后的参数，SQL Server会按照这个时间来完成CheckPoint过程，如果时间指定的短，则SQL Server会使用更多的资源优先完成CheckPoint过程。
更短的恢复间歇意味这更短的恢复时间和更多的磁盘IO，而更长的恢复间歇则带来更少的磁盘IO占用和更长的恢复时间.
SELECT * FROM sys.backup_devices
SELECT * FROM msdb..backupset
ALTER DATABASE TEST ONLINE
