SQL scripts for full and differential backup databases

In the work, SQL Job needs to be created to back up the database regularly. Now, the script is recorded as follows.

1. Full backup:

-- FULL
declare @filename    varchar(1024),
        @file_dev    varchar(300)

declare @path        varchar(1024)
set     @path = N'F:\Backup\Plan2\';

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 + 'SmartDev_Full_' + @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

 

2. Differential backup:

-- Differential
declare @filename    varchar(1024),
        @file_dev    varchar(300)

declare @path        varchar(1024)
set     @path = N'F:\Backup\Plan2\';

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 + 'SmartDev_Differential_' + @filename
backup database [SmartDev] to disk = @file_dev 
with differential, noformat, init,  name = N'SmartDev-Database Differential backup'

go

 

Two SQL jobs can be created in the above two paragraphs respectively. For example, the job of full backup can be scheduled to run in the early hours of every day every week. After successful execution, delete the previous backup file (there are deleted statements in the code). The job of differential backup is scheduled to run in the early hours of every day from Monday to Saturday.

Tags: SQL Sever Database SQL

Posted on Wed, 27 May 2020 09:00:31 -0700 by benwhitmore