Timestamp Filename SQL Backup

Timestamp Filename

Can you create a date timestamp filename for SQL server backup files?  A client recently asked if this was possible since Microsoft SQL Server Management Studio does not provide a formal way of making a timestamp filename. Turns out that the solution to creating a timestamp filename is rather simple and truth be told kind of fun because you can experiment a little with the code to tweak the result to your liking.

Example

The following example declares a variable and sets its value using string concatenation and the Microsoft CONVERT function to generate a filename derived from the CURRENT_TIMESTAMP function. The reason I chose to use the CONVERT function instead of CAST is because CONVERT lets you make use of the optional  <style> argument to configure the datetime output to your liking.

USE Master;
GO

--Full Backup
DECLARE @fname sysname
SET @fname = N'C:\BackupDemo\ADW_Tlogs\adw_full_' + CONVERT(nvarchar,CURRENT_TIMESTAMP,110) + N'.bak'

BACKUP DATABASE ADW_BackupDemo
TO DISK = @fname
WITH INIT;
GO

Remarks

This technique could be used in many other ways in your coding of Transact-SQL.  I hope it gets you to thinking about the possibilities.  For example see if you can add some additional code to dynamically set a 90 day MEDIASET expiration at run time.

Remember to first declare  a variable to hold the value and then set the value of the variable equal to a date that is 90 days from moment the BACKUP DATABASE statement is executed

HINT: DATEADD(dd,90,CURRENT_TIMESTAMP)

Quick Note: You may have noticed my preferred use of the CURRENT_TIMESTAMP function over the GETDATE() function. While both will work equally well within the confines of a Microsoft SQL Server environment CURRENT_TIMESTAMP is standards based and therefore provides better portability.

Parting Shot

I had a ton of fun mulling this one over and writing it. I hope this was a quick, fun read for as well. Now here is a something else for you to think about.

~ I’m so lucky I am riding a gravy train with biscuit wheels.

Share the Knowledge

Leave a Reply