完整备份和差异备份数据库的SQL脚本

工作中需要创建SQL Job对数据库进行定期备份,现把脚本记录如下。

  1. 完整备份:

    -- FULL declare @filename varchar(1024),

         @file_dev    varchar(300)
    

    declare @path varchar(1024) set @path = N'F:BackupPlan2';

    declare @extension_name varchar(16) set @extension_name = N'bak';

    set @filename = convert(varchar(1024), getdate(), 120) set @filename = replace(@filename, ':', '') set @filename = replace(@filename, '-', '') set @filename = replace(@filename, ' ', '')

    set @filename = @filename + '_' + convert (varchar(3), datepart(ms, getdate())) + N'.' + @extension_name

    -- start backup, COMPRESSION is the parameter set @file_dev = @path + 'SmartDevFull' + @filename backup database [SmartDev] to disk = @file_dev with noformat, init, name = N'SmartDev-Database full backup', COMPRESSION

    -- delete the old backup file 1 days ago declare @olddate datetime select @olddate=getdate()-1

    -- execute delete select @path select @extension_name select @olddate execute master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1 go

 

 

 

以上两段可以分别创建两个SQL Job,比如完整备份的作业可以定在每周天凌晨运行,执行成功后删掉之前的备份文件(代码中有删除的语句),差异备份的作业定在周一到周六每天凌晨运行。

  1. 金沙官网线上,差异备份:

    -- Differential declare @filename varchar(1024),

         @file_dev    varchar(300)
    

    declare @path varchar(1024) set @path = N'F:BackupPlan2';

    declare @extension_name varchar(16) set @extension_name = N'bak';

    set @filename = convert(varchar(1024), getdate(), 120) set @filename = replace(@filename, ':', '') set @filename = replace(@filename, '-', '') set @filename = replace(@filename, ' ', '')

    set @filename = @filename + '_' + convert (varchar(3), datepart(ms, getdate())) + N'.' + @extension_name

    -- start backup, COMPRESSION is the parameter set @file_dev = @path + 'SmartDevDifferential' + @filename backup database [SmartDev] to disk = @file_dev with differential, noformat, init, name = N'SmartDev-Database Differential backup'

    go

本文由金沙官网线上发布于数据库,转载请注明出处:完整备份和差异备份数据库的SQL脚本

您可能还会对下面的文章感兴趣: