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

How to Add Date to Database Backup Filename? - Interview Question of the Week #109 backupicon 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.

SQL SERVER – Steps to Backup to Windows Azure storage

SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio

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)

, ,
Previous Post
How to Enable Auto Update Statistics and Auto Create Statistics with T-SQL – Interview Question of the Week #108
Next Post
How to Keep Certain Records on Top While Ordering Data? – Interview Question of the Week #110

Related Posts

1 Comment. Leave new

  • Yep…most people don’t realize you need to remove ‘/’ and ‘:’

    Reply

Leave a Reply

Menu