See SQL Server Backup File Date and Time

I want to be able to see when a backup file was created. Does SQL Server provide a way to add the current date and time to my backup file filenames?

SQL Server records the date and time inside the backup file, but to see the information, you have to look in the backup file by using the following statement:

RESTORE HEADERONLY FROM DISK =  N'c:\temp\TEST-201406-192507.bak'

This statement returns the BackupStartDate and BackupFinishDate as columns.

Another methods using history tables is given below.

SELECT [rs].[destination_database_name]
    ,[rs].[restore_date]
    ,[bs].[backup_start_date]
    ,[bs].[backup_finish_date]
    ,[bs].[database_name] AS [source_database_name]
    ,[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

However, this method doesn't let you easily identify when a backup file was created.Many people want to display the date and time in the file system name of the backup file so that they can easily see the backups ordered in time.Following scripts creates a dynamic SQL statement that makes a backup of a database and encodes the current date and time in the backup filename.The script creates a filename for the backup in the format databasename-YYYYMMDD-HHMMSS .bak. In addition, the script adds a leading zero to the time elements (hours, minutes, and seconds) so that 1:02 A.M. shows as 010200 instead of 10200.The leading zero ensures that the filenames will sort in the correct order in the file system. Note that the script assumes the C:\backup directory exists, so you need to change the directory to put the filenames into the correct path for your environment.

DECLARE @FileName NVARCHAR(256)    ,@NSQL NVARCHAR(4000)
SELECT @FileName = 'c:\temp\' 
+ db_name() 
+ N'-' + CONVERT(NCHAR(6), getdate(), 112) 
+ N'-' + right(N'0' + rtrim(CONVERT(NCHAR(2), datepart(hh, getdate()))), 2) 
+ right(N'0' + rtrim(CONVERT(NCHAR(2), datepart(mi, getdate()))), 2) 
+ right(N'0' + rtrim(CONVERT(NCHAR(2), datepart(ss, getdate()))), 2) + N'.bak'
PRINT @FileName
SELECT @NSQL = 'BACKUP DATABASE ' + QUOTENAME(db_name(), '[') + ' TO DISK = ''' + @FileName + ''''
PRINT @NSQL
EXEC (@NSQL)

Reference http://www.mssqltips.com/sqlservertip/1150/what-is-in-your-sql-server-backup-files/