/*
SQLServer 的备份与恢复
先写入日记文件，再写入数据库
恢复模式
完整: 数据总是存储为2份，一份在日志文件里，一份在数据文件
大容量日记: 普通SQL更新数据会完整记录日记，BCP/BULKINSERT/INTO/创建或重建索引等不会写入实际数据更新内容，
而只是在一个数据页位图图标标志区域作一个数据变更标识，以减少IO负荷，日记备份时，根据数据页位图去更新数据
简单模式: 对数据更改还是按照大容量模式记录日记，数据更新操作完成，日记文件即可重用，提供全备份+差异备份
各种备份区别
完整备份: 备份整个数据库，恢复模式必须为完整
事务备份: 备份事务交易记录，恢复模式必须为完整
差异备份: 恢复时不需要依备份顺序逐个恢复（如需恢复到星期三，先还原星期天的完整备份，再直接恢复星期三的差异备份）
增量备份: 恢复时必须依备份顺序逐个恢复（如需恢复到星期三，先还原星期天的完整备份，然后还原星期一/二的备份，最后才恢复星期三的增量备份）
*/
--备份
Backup Database initSfis To
Disk='F:\Database\initSfis_1.Bak',
Disk='F:\Database\initSfis_2.Bak',
Disk='F:\Database\initSfis_3.Bak'
With Compression --压缩Differential 为差异备份
--多存储一份备份镜像，必须加参数FORMAT
Backup Database initSfis To
Disk='F:\Database\initSfis_1.Bak',
Disk='F:\Database\initSfis_2.Bak',
Disk='F:\Database\initSfis_3.Bak'
MIRROR TO
Disk='F:\Database\initSfis_1_1.Bak',
Disk='F:\Database\initSfis_2_2.Bak',
Disk='F:\Database\initSfis_3_3.Bak'
With Compression, format --压缩
--日记备份, 同样可以使用MIRROR 选项
Backup Log DB To
Disk='D:\Data\DB_20140519_Log_3_1.Bak',
Disk='D:\Data\DB_20140519_Log_3_2.Bak'
With Compression
--查看文件备份头
Restore HeaderOnly From Disk='F:\Database\initSfis20131031.bak'
GO
--查看文件备份是否有效
Restore VERIFYONLY From Disk='F:\Database\initSfis20131031.bak'
GO
--查看文件中的数据文件信息
Restore FileListOnly From Disk='F:\Database\initSfis20131031.bak'
GO
--还原
RESTORE DATABASE [initSfis] FROM
DISK =N'F:\Database\initSfis_2.Bak',
DISK =N'F:\Database\initSfis_3.Bak',
DISK =N'F:\Database\initSfis_1.Bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, replace -- FILE=1 为如有多次备份内容，指示使用第一次备份
GO
--还原数据库/事务日记指定时间点
RESTORE DATABASE [Credit] FROM DISK = N'F:\Credit.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10 --可以还原事务日记WITH NORECOVERY
GO
RESTORE DATABASE [Credit] FROM DISK = N'F:\Credit_backup_2012_07_06_011501_0146598.trn'WITH NORECOVERY
GO
RESTORE DATABASE [Credit] FROM DISK = N'F:\Credit_backup_2012_07_06_021503_5289873.trn'WITH RECOVERY,STOPAT='2012-07-06 02:00:00' --锁定到哪一点
GO
--还原没有数据库的前提，需要还原的时候移动文件
RESTORE DATABASE [Credit] FROM DISK = N'F:\Credit.bak' WITH FILE = 1,
MOVE 'Credit' TO 'D:\ProgramFile\...\Credit.mdf',
MOVE 'Credit_Log' TO 'D:\ProgramFile\...\Credit_1.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
/*
* 文件组的备份与还原，测试过程如下
* 1、建立空白数据库后，作完整备份
* 2、建立数据表后，做差异备份
* 3、插入记录后，做事务日记备份
USE BurningDB
GO
CREATE DATABASE BurningDB
GO
-- 执行完整备份
CREATE TABLE T1(
p_id int,
p_name varchar(10)
)
CREATE TABLE T2(
s_id int,
s_name varchar(10)
)on FG1
-- 执行差异备份
insert into T1 values (1, 'tony')
insert into T1 values (3, 'peter')
insert into T2 values (2, 'wang')
insert into T2 values (4, 'dong')
select * from T1
select * from T2
--drop table T1
--drop table T2
-- 最后执行日记备份
*/
USE master
GO
-- * 备份
-- 备份完整备份
BACKUP DATABASE [BurningDB]
FILEGROUP = N'PRIMARY',
FILEGROUP = N'FG1'
TO DISK = N'F:\Database\BurningDB_Full.bak'
WITH NOFORMAT, NOINIT, NAME = N'BurningDB-完整文件组备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- 备份差异备份
BACKUP DATABASE [BurningDB]
FILEGROUP = N'PRIMARY',
FILEGROUP = N'FG1'
TO DISK = N'F:\Database\BurningDB_DIFF.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'BurningDB-差异文件组备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- 备份日记备份
BACKUP LOG [BurningDB]
TO DISK = N'F:\Database\BurningDB_LOG.bak'
WITH NOFORMAT, NOINIT, NAME = N'BurningDB-事务日志数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- * 还原
-- 还原完整备份
RESTORE DATABASE [BurningDB]
FILE = N'BurningDB',
FILE = N'FG1' FROM DISK = N'F:\Database\BurningDB_Full.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
-- 还原差异备份
RESTORE DATABASE [BurningDB]
FILE = N'BurningDB',
FILE = N'FG1'
FROM DISK = N'F:\Database\BurningDB_DIFF.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
-- 还原日记备份
RESTORE LOG [BurningDB]
FROM DISK = N'F:\Database\BurningDB_LOG.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
/*
* 差异备份
* 只有一个PRIMARY 文件组
-- 备份
USE master
GO
BACKUPDATABASE [BurningDB]
TO DISK = N'F:\Database\b_full.bak'
WITH NOFORMAT,NOINIT, NAME = N'BurningDB-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUPDATABASE [BurningDB]
TO DISK = N'F:\Database\b_diff.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'BurningDB-差异数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- 还原
USE master
GO
RESTOREDATABASE [BurningDB]
FROM DISK = N'F:\Database\b_full.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD,REPLACE, STATS = 10
GO
RESTOREDATABASE [BurningDB]
FROM DISK = N'F:\Database\b_diff.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
*/
