Appending the Date to your Database backup in SQL Server 2005

In SQL Server 2005 Management Studio, there is a really simple GUI to create database backups and a nifty little button that let’s you script that action into a job.  You can see exactly how to do that here: http://support.microsoft.com/kb/930615

However, if you want to create a job that not only backs up your data but appends the job date to the filename (so the file isn’t overwritten each time the job runs and/or you’re unwilling to put your faith in one be-all-end-all backup file), you’ll need a bit of T-SQL magic.

1. Create a “New Job” in Management Studio
2. In the “Steps” submenu, click New and insert the following:

DECLARE @MyBackup nvarchar(250)

SET @MyBackup = ‘C:\Content Backups\MyDatabase_Content_’ + convert(varchar(10), getdate(), 112) + ‘.BAK’

BACKUP DATABASE [MyDatabase_Content] TO  DISK = @MyBackup
WITH NOFORMAT, NOINIT,  NAME = N’MyDatabase_Content-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Substitute your file location for @MyBackup and choose BACKUP options as you see fit, test the job, and voila, MyDatabase_Content_jobdate.bak!