How to add a timestamp to a backup name

Schedule a SQL Server job to create database backups. Instead of using a script such as:

BACKUP DATABASE [AdventureWorks2012]

TO DISK = N'E:\Test\AdventureWorks.bak' 

Use :

DECLARE @SQLStatement VARCHAR(2000)

SET @SQLStatement= 'E:\Test\AdventureWorks' +_ CONVERT(nvarchar(30), GETDATE(), 110) +'.bak'

BACKUP DATABASE [AdventureWorks2012] TO DISK = @SQLStatement

The variable and CONVERT(nvarchar(30), GETDATE(), 110) allow to add the current date. The backups created are named like below.

AdventureWorks_07-29-2013

AdventureWorks_07-30-2013

AdventureWorks_07-31-2013