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
上一篇: 推荐阅读微商,谈谈我的见解