Create Automatic SQL Database BackUp

Step: 1

CREATE PROCEDURE usp_DatabaseBackup As

DECLARE @filename nvarchar(3000)

SET @fileName = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\'+ Cast(DATEPART(m, GetDate())as

nvarchar)

+ '-' + Cast(DATEPART(d, GetDate()) as nvarchar)

+ '-'+ Cast(DATEPART(yy, GetDate()) as nvarchar)+'-Test.Bak'

BACKUP DATABASE [Test] TO DISK = @fileName

--Test is the name of database
GO

Step: 2
Write below code in Notepad and save it with extention .bat (as executable batch) file

sqlcmd -s"sqlexpress instance name" -E -Q"exec usp_DatabaseBackup"

Step 3

Go to windows scheduler and schedule the time to run scheduler

It will create automatic back on selected location.

Note: Please delete old back up files from location if it is not require.