5

When using a SQL Server 2012 EMaintenance Plan for backup purposes, the Backup Database Task creates a unique filename. I understand the format all the way to the final set of numbers.

For example, a Full Database Backup initiated via the Backup Database Task would create a filename like:

AdventureWorks_backup_2014_12_17_080005_5862824.bak

What is 5862824 referencing? Is than LSN? Something else?

  • 1
    While I do not know the answer to this, I do know that the last set of numbers is not LSN. You can query the DMV msdb.dbo.backupset and see the filenames and the LSNs associated with each backup and they do not match anything. This is a really good question though! – Kris Gruttemeyer Dec 17 '14 at 16:38
4

Concurring with Aaron's assessment of ask the developers.

I used the wizard to create a full backup plan, exported that to my machine and then opened it up in Visual Studio/BIDS/SSDT-BI. You'll see the Backup Database Task looking something like below

enter image description here

When you click that View T-SQL button, it shows the command it will execute. Running a Profiler session, I couldn't correlate the button click to activity in my database but that may just be my profiler weakness.

I can say that if you are in this screen and stop the SQL Server Service, clicking View T-SQL will likely crash your Visual Studio session. So, that gives credence to Aaron's assumption on it being part of the time component. I would get different values generated and the target database had no activity in which would indicate a reliance on LSN.

Furthermore, the task doesn't allow you to modify the generated SQL. SSIS packages allow a variety of configuration via Expressions but a quick glance through the available ones

enter image description here

enter image description here

nothing really jumps out as something I'd want to modify.

Ultimately, if you want customization in your maintenance plan, skip the wizard and write the SQL yourself. If you stick with SSIS as the coordination engine, you'll want Execute SQL Tasks and I'm a fan of using Biml to automate the creation of such things.

  • +1 thanks Bill, and I also had no luck with Profiler except I did notice calls to get the system time. – Aaron Bertrand Dec 17 '14 at 18:21
  • @billinkc Thanks for your help with trying to figure this out. This was mainly to keep a filename structure with other backup sets that were out of my control. I appreciate the insight. – Travis Crooks Dec 18 '14 at 0:48
9

Since this logic is buried within the code for the SSIS package behind maintenance plans, I think you will only get guesses unless the author happens to swing by here. Here is my educated guess based on a few experiments:

For SQL Server 2008 and above, the 7 digits are the sub-second portion of the datetime2(7) output of SELECT SYSDATETIME(); at the moment the T-SQL for the backup command is actually generated. If you create a subplan for all user databases, and then go in to edit and choose View T-SQL, you will see this:

BACKUP DATABASE [SQLSentry] TO  DISK = N'<path>\SQLSentry_backup_2014_12_17_114845_6488663.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'SQLSentry_backup_2014_12_17_114845_6488663', 
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Views] TO  DISK = N'<path>\Views_backup_2014_12_17_114845_6644778.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Views_backup_2014_12_17_114845_6644778', 
SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

You'll notice that component of the filename, which happens to conveniently come after HHMMSS, is increasing with each subsequent command, but the numbers are still grouped together to some extent. If you repeat this a few times you will also see that they change every time, that they are not ever-increasing, and that they loop around to even show 7-digit numbers with leading 0s. Which indicates to me that it is not a sequence of any kind, and certainly not any component of the LSN or other meaningful properties of the database itself.

I couldn't think of a very easy way to have the SSIS package also include some output that would indicate SYSDATETIME() at the point of generation, and even if I could, I would have no idea if it would generate that value before or after, and how long it would take or what other delays it would introduce. So I'm not sure there is a way to confirm that's what it is.

  • Thanks for your help in figuring this out! I appreciate your thoughts on this. – Travis Crooks Dec 18 '14 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.