DOWNLOAD THE CODE:
Download the Code 50420.zip

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

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Nice script, but the code needs a tweak for the date parameter. Currently it needs to use a format style of 12 (instead of 112) or be bumped from 6 to 8 chars long. And the exec statement is missing the close-paren (but I'm just being nit-picky now). :)

I've written something similar for t-log backups (& alerts that call t-log backups) that creates an output very similar to the "Maintenance Plan" filename output. Myself, I prefer to have the milliseconds added to my timestamps in case an alert fires that creates a backup file, this prevents me from stepping on files previously created via regular backup or another alert call...

DECLARE @TimeStamp char(17)

SET @TimeStamp = (SELECT REPLACE(CONVERT(varchar(10),getdate(),101),'/',')) + (SELECT REPLACE(CONVERT(varchar(12),getdate(),114),':','))

PRINT @TimeStamp

Good stuff Gert, keep em coming.

Haywood- August 22, 2006

Article Rating 3 out of 5