How to create an auto backup plan in SQL Server?

This article will walk you through how to create an auto backup plan through SQL Server Agent.

For this example, we will create a full backup every week, a differential backup every day, and a log every half an hour.

Now let's create three script files for the code. The files can be named "SAWFull.sql", "SAWDiff.sql" and "SAWLog.sql". The scripts are as follows:

--For full backup

declare @strTime nvarchar(14), @dt datetime, @strDBBakPath nvarchar(256)

set @dt = getdate()
set @strTime = datepart(year, @dt) *10000 + datepart(month, @dt) * 100 + datepart(day, @dt)
set @strDBBakPath='D:\Backup\'+@strTime+'SourceAnywhere_f.bak'
BACKUP DATABASE SourceAnywhere TO DISK = @strDBBakPath

--For differential backup

declare @strTime nvarchar(14), @dt datetime, @strDBBakPath nvarchar(256)

set @dt = getdate()
set @strTime = datepart(year, @dt) *10000 + datepart(month, @dt) * 100 + datepart(day, @dt)
set @strDBBakPath='D:\Backup\'+@strTime+'SourceAnywhere_d.bak'

BACKUP DATABASE SourceAnywhere TO DISK = @strDBBakPath with DIFFERENTIAL

--For log backup

declare @strTime nvarchar(14), @dt datetime, @strDBBakPath nvarchar(256)
set @dt = getdate()
set @strTime = datepart(year, @dt) *10000 + datepart(month, @dt) * 100 + datepart(day, @dt)
set @strDBBakPath='D:\Backup\'+@strTime+'SourceAnywhere_l.bak'

BACKUP LOG  SourceAnywhere TO DISK = @strDBBakPath

With the above backup plan, basically you are able to restore your database at any time.