THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Backup File Naming Convention

I have been asked this many times before and again just recently so I figured why not blog about it. None of this information outlined here is rocket science or even new but it is an area that I don’t think people put enough thought into before implementing.  Sure everyone choses some format but it often doesn’t go far enough in my opinion to get the most bang for the buck. This is the format I prefer to use:

ServerName_InstanceName_BackupType_DBName_DateTimeStamp_nn.xxx

ServerName_InstanceName = Pretty self explanatory but lets look at it. Let’s say that the machine name is M432 and the instance is Dev2008. That would normally be W432\Dev2008 however I don’t like special characters so I change it to W432_Dev2008.  If it was a default instance it would be W432_W432. Some people (including myself) prefer to leave off the Server Name if it is a default instance but that is up to you. Since the default instance is always the name of the server it’s still pretty clear where it came from.

BackupType = FULL, DIFF or LOG.  Nothing more is needed here.

DBName = Full name of the database. One note here is that if the name has spaces I like to replace the space with some other valid character and some people prefer to remove the space altogether. Spaces in an object name is a whole debate in itself and I wont go there now Smile.

DateTimeStamp = yyyymmddhhmmss. This allows me to know exactly when the backup started just by looking at the name and makes it unique as well. I don’t know any one who takes two backups of the same db in less than a second so this convention works to avoid file name conflicts.

_nn = The individual number associated with backing up to multiple files for a single backup operation. Typically backing up to multiple files for a FULL or DIFF backup can be more effecient with larger dbs so appending a number from 01 to nn ensures uniqueness as the rest of the name will be the same. If it is a single file then you can simply use 01 or omit that part altogether.

.xxx = I also like to use the extension to identify the tool used to create the backup file. For native SQL Server backups I use .bak regardless of the type of backup (Log, Diff or Full). For backups done using a 3rd party utility such as the one from Red-Gate I would use .sqb and so on.

This naming convention allows me to do several things. First it ensures each backup file will have a unique name. Second it allows me with a quick glance to see where the backup originated, what type of backup it is, which database it is for, when the backup started and which tool was used to create the backup.  So again there is nothing particularly new to this approach but I often see the naming falling short of this and generally only having the DB name and timestamp. Why not take the extra few steps to ensure you get the most out of your naming that you can. The code to generate the whole file name is pretty simple and can be done dynamically so why not go this route? OK there are always exceptions so let’s not start a debate war Smile.

Have fun,

Andy

Published Friday, October 05, 2012 4:41 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Stephen Morris said:

Easiest to automate this using SQL Agent Job tokens

http://technet.microsoft.com/en-us/library/ms175575.aspx

October 6, 2012 4:43 PM
 

Ted said:

Nice touch on the Server/instance name.  That is the one thing I've forgotten.. Being from smaller environments with limited servers it wasn't much of an issue, as I would typically dump all the backups into folders with the server's name on my backup server, but I see how it would be very useful to name the files that way too.

Very good info especially for the new DBA who is getting started.  Little things like this will save a ton of time in a crisis!

October 8, 2012 7:54 AM
 

RichB said:

I think you need to clarify your strategy somewhat - as that naming convention only makes any sense if the following are true:

1. Only One backup action per file.

2. Only One Database per file

3. Only One FILE per backup action

However, there is nothing inherent to stop you running many backups of many databases to the same file over many days.

What you are proposing here seems an awful lot like hungarian notation taken to a whole new level.

Its what we do too :)

October 8, 2012 10:22 AM
 

Andrew Kelly said:

Rich,

Yes thanks for the reminder. I will amend the topic as I did forget to talk about the _nn for multiple files. I didn't want to get into too much detail about backups per se just the naming.  However I never recommend having more than 1 backup or action in a file as a general practice. Sure there may be exceptions but usually not as a regular practice. For one doing so can complicate the cleanup process as you can't delete an individual backup from a file with more than 1 backup in it. Again that can be a whole topic in and of itself.

October 8, 2012 10:51 AM
 

Ian Yates said:

I've been using Ola Hallengren's scripts more and more lately (http://ola.hallengren.com) which follow a similar, but not quite identical, naming convention.

October 8, 2012 8:20 PM
 

Niels Grove-Rasmussen said:

I like to format the timestamp as ISO 8601 (http://en.wikipedia.org/wiki/ISO_8601), e.g. yyyy-mm-ddThhmmss.

Ola Hallengren's scripts is still a quick and robust solution.

October 11, 2012 5:28 PM
 

Back up Files said:

Excellent tips. I use the database plugin, but I don’t use the other one to back up my files… I probably should.

May 11, 2013 12:19 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement