How to Add Date to Database Backup Filename? – Interview Question of the Week #109

Interview question: How can I add a date to a SQL Server database backup filename?

Answer: Build the filename from one captured timestamp, then pass the resulting URL to BACKUP DATABASE. A new blob name lets each backup remain separate.

This question came from a reader’s comment on my Azure backup articles. Their job always targeted master.bak. The next run returned an error saying the file already existed at the remote endpoint. They did not want WITH FORMAT to overwrite the previous copy. The answer I sent by email was to give every run a fresh name; the reader then used it in a daily SQL Agent job.

Here is the same idea with a sortable UTC timestamp. Replace the account and container placeholders, and configure the appropriate SQL Server credential for your Azure Blob Storage setup before executing the backup. Keep the safe filename stem separate from the database name: a valid database identifier is not automatically a valid URL path.

DECLARE @Database sysname = N'master';
DECLARE @SafeStem nvarchar(100) = N'master';
DECLARE @Utc datetime2(3) = SYSUTCDATETIME();
DECLARE @Stamp char(18) = CONVERT(char(8), @Utc, 112) + N'_' +
                          REPLACE(CONVERT(char(12), @Utc, 114), N':', N'');
DECLARE @TargetUrl nvarchar(2048) =
    N'https://YOUR-ACCOUNT.blob.core.windows.net/YOUR-CONTAINER/' +
    @SafeStem + N'_FULL_' + @Stamp + N'.bak';

SELECT @TargetUrl AS BackupTarget;

-- After reviewing the target and configuring its credential:
-- BACKUP DATABASE @Database
-- TO URL = @TargetUrl
-- WITH CHECKSUM, COMPRESSION, STATS = 10;

The stamp begins with year, month, and day, so similarly named backups sort in time order. The time component has no colons. Capturing SYSUTCDATETIME() once prevents the date and time portions from crossing a boundary between separate clock reads. If a job can start more than once in the same millisecond, add another unique suffix rather than assuming the timestamp alone is sufficient.

A dated name helps identify a candidate backup. It does not replace backup validation or a tested restore. Azure authentication and available backup options vary with SQL Server version and storage configuration; check those before using this in a scheduled job.

The original discussion began with my guides to backup to Azure storage and backup from SQL Server 2014 Management Studio.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.