Backup Database in SqlServer with Date and Time Information in the File Name

The build-in back-up mechanism of SqlServer allows to either override the back-up file or append to it. I usually prefer to have each back-up in it’s file, this way I can easily choose which older back-ups to delete, and immediately see the last one. The following script makes a back-up with a file name that contains date and time information and can be used in a job step:

declare @currentDate datetime
set @currentDate = GetDate()

declare @fileName varchar(255)
set @fileName = 'C:\BKP\MyDatabase' 
	+ cast(Year(@currentDate) as varchar(4)) 
	+ Replicate('0', 2 - Len(cast(Month(@currentDate) as varchar(2)))) 
            + cast(Month(@currentDate) as varchar(2)) 
	+ Replicate('0', 2 - Len(cast(Day(@currentDate) as varchar(2)))) 
            + cast(Day(@currentDate) as varchar(2)) 
	+ '_' +
	+ Replicate('0', 2 - Len(cast(DatePart(hour, @currentDate) as varchar(2)))) 
            + cast(DatePart(hour, @currentDate) as varchar(2))
	+ Replicate('0', 2 - Len(cast(DatePart(minute, @currentDate) as varchar(2)))) 
            + cast(DatePart(minute, @currentDate) as varchar(2)) + '.bak'

backup database [MyDatabase] to disk = @fileName with NOFORMAT, NOINIT, 
    name = N'MyDatabase-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The back-up will be written to the file with a name like C:\BKP\MyDatabase_20090505_1346.bak.

Hope it helps.