Post reply

Default Naming Convention of backup files

  • April 9, 2009 at 4:28 am

    #208241

    HI,

    There seems to be a different naming convention for the backup file that is generated through Maintenance Plan in SQL Server 2008 and previous versions.

    For SQL Server 2008 an example of backup file generated is shown below

    DB_backup_2009_04_09_020001_2310776

    Here I understood the convention except the last value ie. 2310776.

    Can somebody please let me know what this value stands for ?

    Thanks..

  • April 13, 2009 at 12:56 am

    #976405

    Please assist ...

  • April 18, 2009 at 4:06 am

    #980145

    Guys,

    Does anyone has an answer of my query ???????????

  • April 18, 2009 at 9:03 am

    #980193

    I hadn't noticed that. No idea what that is from. I assume date, then time, but the last digits, not sure. Have you checked in the job history or maintenance plans? Perhaps it relates back to one of those items.

  • April 21, 2009 at 4:22 am

    #981285

    HI,

    Thanks for reply ....

    Have checked the job history and maintenance plan. Still no clue. Didn't find any relation amongst these files.

    Any more suggestions ???

  • June 24, 2010 at 8:36 am

    #1184543

    I know this is late in the day, and you asked a year ago, but I was looking for the answer too and came accross your post.

    From some poking I reckon it's the fractional seconds from a datetime2 timestamp i.e SYSDATETIME.

    SELECT SYSDATETIME()

    Reckon rather than setting a variable to hold the name and then insert into each table, they've done things on the fly seperately, using SYSDATETIME(). It explains why the name in msdb.dbo.backupset is different to the physical_device_name from msdb.dbo.backupmediafamily, as the insert occured fractionaly later.

    Format is:

    DBName + '_backup_' + YYYY + '_' + MM + '_' + DD + '_' + HHMMSS + '_' + nnnnnnn

    Doing a CONVERT(varchar,SYSDATETIME(),114) preserves the nanon seconds: 15:21:34.6965687

    As does CONVERT(varchar,SYSDATETIME(),113): 24 Jun 2010 15:21:34.6965687

    and also CONVERT(varchar,SYSDATETIME(),109): Jun 24 2010 3:21:34.6965687PM

    To produce the same:

    SELECT name + '_backup_'

    + REPLACE(CONVERT(varchar,SYSDATETIME(), 102),'.','_') + '_'

    + REPLACE(REPLACE(CONVERT(varchar,SYSDATETIME(), 114),':',''),'.','_')

    FROM sys.databases

    --master_backup_2010_06_24_152621_5545687

    --model_backup_2010_06_24_152621_5545687

    --msdb_backup_2010_06_24_152621_5545687

    --mydb_backup_2010_06_24_152621_5545687

  • March 11, 2014 at 11:44 pm

    #1696217

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply