Easy Reading: See Backup 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:\backup\LibraryBackup80
    .bak'

This statement returns the BackupStartDate and BackupFinishDate as columns.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.

The script in Listing 1 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.

Gert E.R. Drapers
Architect/Development Manager
Visual Studio Team Edition for Database Professionals