当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQL Server的3中备份方式

SQL Server的3中备份方式

2020年07月18日  | 移动技术网IT编程  | 我要评论

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

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网