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.
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
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
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.
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.