Question: How to Add Date to Database Backup Filename?
Answer: Many times, there are comments on my earlier blogs which are a source of new blogs. This blog is a side effect of one of such interaction.
Here are a few blogs which I wrote about backup to Azure storage using Backup to URL command.
Here is the comment which inspired me for this blog
<BEGIN – Comment>
I am trying to take backup to a URL but how can I generate a new file name? Getting below error
Msg 3271, Level 16, State 1, Line 2
A nonrecoverable I/O error occurred on file “https://mycblobstorage.blob.core.windows.net/sqlbackup/master.bak:” The file exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed..
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
<END – Comment>
If we use WITH FORMAT then it would overwrite the backup. So, here is the solution I have provided via email to him.
DECLARE @BackupName varchar(100) DECLARE @BackupFile varchar(100) DECLARE @dateTime NVARCHAR(20) DECLARE @backupLocation nvarchar(200) DECLARE @DBNAME VARCHAR(200) DECLARE @URL VARCHAR(MAX) DECLARE @sqlCommand NVARCHAR(1000) SET @DBNAME = 'MASTER' SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') SET @BackupFile = +REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK' SET @URL = 'https://SQLAuthority.blob.core.windows.net/sqlbackup/' + @BackupFile SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO URL = '''+@URL+ ''' WITH COMPRESSION, NOSKIP, NOFORMAT,CREDENTIAL = ' + '''SQLAuthCred''' EXEC(@sqlCommand)
Every time we would run the backup, it would append the date and time to the backup file so that new have new file name every time. He scheduled this in a SQL Agent job which was running every day and taking backup successfully.
Reference: Pinal Dave (https://blog.sqlauthority.com)