Backup Database Task - What's the number at the end of the filename?

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?

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

StackExchange Database Administrators Q#86432, answer score: 10