SQL Server的3中备份方式

迷南。 2023-02-26 16:19 25阅读 0赞

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

# 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