SQLServer_Backup备份方案

USE [master]

SELECT

bs.database_name AS 'Database Name',

bs.backup_start_date AS 'Backup Start',

bs.backup_finish_date AS 'Backup Finished',

DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)',

bmf.physical_device_name AS 'Backup File',

CASE 

    WHEN bs.[type] = 'D' THEN 'Full Backup' 

    WHEN bs.[type] = 'I' THEN 'Differential Database' 

    WHEN bs.[type] = 'L' THEN 'Log' 

    WHEN bs.[type] = 'F' THEN 'File/Filegroup' 

    WHEN bs.[type] = 'G' THEN 'Differential File'

    WHEN bs.[type] = 'P' THEN 'Partial'  

    WHEN bs.[type] = 'Q' THEN 'Differential partial' 

END

AS 'Backup Type'

FROM msdb.dbo.backupmediafamily bmf WITH(NOLOCK)

    INNER JOIN msdb..backupset bs WITH(NOLOCK)

    ON bmf.media_set_id = bs.media_set_id

WHERE bs.database_name = 'test-recovery'

ORDER BY bs.backup_start_date ASC;

 

-- note for 下次看 

-- backup 有 3 种, full, log, differential 

-- 通常是 full -> log -> log -> differential -> log -> log -> full 

-- 比如 1 天 1 个 full, 半天一个 differential, 每小时一个 log 

-- 备份的关键是, 1.恢复的时间(尽可能短) 2. 恢复的层度 (尽可能完整)

-- express, web edition 不支持 backup compression 

-- norecovery 意思是还有下一个 restore 要跑, 最后一个 restore 才 recovery 

-- refer http://mysql.taobao.org/monthly/2017/11/03/

-- refer http://mysql.taobao.org/monthly/2017/12/05/

-- stats 是显示 progress % stats 10 = 完成 10% 就显示一下 

-- init and replace 用于 full 的情况, 另外 2 个 用 noint 和不需要放 replace 

-- STOPAT 是只恢复到某事时间点停止

use [master];

use [test-recovery];

alter database [test-recovery] set recovery full with no_wait 

declare @now nvarchar(50) = REPLACE(convert(nvarchar(20),GetDate(),120),':','-');

set @now = '2018-09-02 18-16-27';

declare @type nvarchar(50) = 'full';

declare @path nvarchar(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_'+ @now +'_' + @type +'.bak';

--backup log [test-recovery] to disk = @path with noinit,stats=10;

--backup database [test-recovery] to disk = @path with differential,init,stats=10;

--backup database [test-recovery] to disk = @path with init,stats=10;

restore database [test-recovery] from disk = @path with norecovery, replace;

INSERT INTO Products(name) VALUES ('dada');

INSERT INTO Products(name) VALUES ('yyyy');

INSERT INTO Products(name) VALUES ('zz'); 

INSERT INTO Products(name) VALUES ('gg');

 

RESTORE DATABASE [test-recovery]

FROM DISK =N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_2018_09_02-16_17_differential.bak' WITH NORECOVERY;

RESTORE LOG [test-recovery]

FROM DISK =N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_2018_09_02_16_18_log.bak' WITH RECOVERY;

RESTORE LOG [test-recovery]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_2018_09_02_16_18_log.bak' WITH STOPAT = '2017-12-17 23:04:46.130', RECOVERY

文件来源：https://www.cnblogs.com/keatkeat/p/9574915.html