欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server的3中备份方式

程序员文章站 2022-06-21 16:46:53
在工作中,通常是完整备份和差异备份相结合,如每周进行一次完整备份,每天进行差异备份。1. 完整备份可定期备份,如每周,每月一次BACKUP DATABASE [BI_ODS] TO DISK = @bak_fileWITH NOFORMAT, NOINIT, NAME = @bak_name, SKIP, REWIND, NOUNLOAD, STATS = 10DECLARE @time_stamp varchar(17) ;-- 文件路径DECLARE @bak_file var...

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

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

本文地址:https://blog.csdn.net/qq_33246702/article/details/107378857