SQL Server的3中备份方式

在工作中，通常是完整备份和差异备份相结合，如每周进行一次完整备份，每天进行差异备份。

1. 完整备份

可定期备份，如每周，每月一次
BACKUP DATABASE [BI_ODS] TO DISK = @bak_file
WITH NOFORMAT, NOINIT, NAME = @bak_name, SKIP, REWIND, NOUNLOAD, STATS = 10

DECLARE @time_stamp varchar(17) ;
-- 文件路径
DECLARE @bak_file varchar(max);
-- 文件名
DECLARE @bak_name VARCHAR(max);
SET @time_stamp = replace(convert(nvarchar(19),GETDATE(),20),':','');
SET @bak_file = 'D:\SQL Server Backup\BI_ODS_backup_' +  @time_stamp + '.bak'
SET @bak_name = 'BI_ODS_backup_' + @time_stamp

BACKUP DATABASE [BI_ODS] TO  DISK = @bak_file 
WITH NOFORMAT, NOINIT,  NAME = @bak_name, SKIP, REWIND, NOUNLOAD,  STATS = 10

2. 增量/差异备份

可每天进行备份，与全量备份相比增加了DIFFERENTIAL 参数
BACKUP DATABASE [BI_ODS] TO DISK = @bak_file
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = @bak_name, SKIP, REWIND, NOUNLOAD, STATS = 10

DECLARE @time_stamp varchar(17) ;
-- 文件路径
DECLARE @bak_file varchar(max);
-- 文件名
DECLARE @bak_name VARCHAR(max);
SET @time_stamp = replace(convert(nvarchar(19),GETDATE(),20),':','');
SET @bak_file = 'D:\SQL Server Backup\BI_ODS_diff_backup_' +  @time_stamp + '.bak'
SET @bak_name = 'BI_ODS_backup_' + @time_stamp

-- 差异备份
BACKUP DATABASE [BI_ODS] TO  DISK = @bak_file
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = @bak_name, SKIP, REWIND, NOUNLOAD,  STATS = 10

3. 事务日志备份

BACKUP LOG [BI_ODS] TO DISK = @bak_file
WITH NOFORMAT, NOINIT, NAME = @bak_name, SKIP, REWIND, NOUNLOAD, STATS = 10

DECLARE @time_stamp varchar(17) ;
-- 文件路径
DECLARE @bak_file varchar(max);
-- 文件名
DECLARE @bak_name VARCHAR(max);
SET @time_stamp = replace(convert(nvarchar(19),GETDATE(),20),':','');
SET @bak_file = 'D:\SQL Server Backup\BI_ODS_log_backup_' +  @time_stamp + '.trn'
SET @bak_name = 'BI_ODS_backup_' + @time_stamp

BACKUP LOG [BI_ODS] TO  DISK = @bak_file 
WITH NOFORMAT, NOINIT,  NAME = @bak_name, SKIP, REWIND, NOUNLOAD,  STATS = 10