THE SQL Server Blog Spot on the Web

Welcome to - 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 = 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,


Published Friday, October 5, 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



Stephen Morris said:

Easiest to automate this using SQL Agent Job tokens

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:


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 ( 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 (, 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

Maryam said:

I am working on SQL server backup feature through SQL management studio 2014. I have managed to create job in management studio which is creating a backup db with default name. But I want to customize the backup db name as this.


Please suggest how it can be achieved.

August 3, 2016 2:20 AM

zzyytt said:


<H1><a href="" title="yeezy boost 350 v2"><strong>yeezy boost 350 v2</strong></a></H1>

<H1><a href="" title="westbrook shoes"><strong>westbrook shoes</strong></a></H1>

<H1><a href="" title="stephen curry shoes"><strong>stephen curry shoes</strong></a></H1>

<H1><a href=""">" title="louboutin shoes"><strong>louboutin shoes</strong></a></H1>

<H1><a href="" title="patriots jerseys"><strong>patriots jerseys</strong></a></H1>

<H1><a href="" title="kobe byrant shoes"><strong>kobe byrant shoes</strong></a></H1>


<H1><a href=""><strong>nike air force 1</strong></a></H1>

<H1><a href=""><strong>michael kors handbags</strong></a></H1>

<H1><a href=""><strong>cartier bracelet</strong></a></H1>

<H1><a href=""><strong>off white clothing</strong></a></H1>


[url=][b]louboutin shoes[/b][/url]

[url=][b]nike hyperdunk 2017[/b][/url]

[url=][b]nike basketball shoes[/b][/url]

[url=][b]adidas pure boost[/b][/url]


<a href=""><strong>adidas superstar</strong></a>

<a href=""><strong>nike lebron 11</strong></a>

<a href=""><strong>nike air max 90</strong></a>

<a href=""><strong>adidas nmd</strong></a>


May 1, 2018 6:57 PM

zzyytt said:">">">">

May 1, 2018 6:57 PM

loemrntdherid1 said:

May 20, 2018 8:25 PM

dongdong8 said:



June 29, 2018 3:33 AM

Dissertation Writing Service said:

PhD Thesis Writing Services  

Dissertation Writing Services

Research paper Writing Service

Master Thesis Writing Service

thesis writing service   

July 5, 2018 3:32 AM

Dissertation Writing Service said:

PhD Thesis Writing Services  

Dissertation Writing Services

Research paper Writing Service

Master Thesis Writing Service

thesis writing service   

July 10, 2018 5:25 AM

dongdong8 said:



July 23, 2018 11:45 PM

Mausam Roy said:

Well-Settled packers and movers in have a good strength of employees and also different dedicated teams for different purposes. Better to know about the company’s manpower as this will help you to get more information about the company More information:

August 8, 2018 5:06 AM

Mausam Roy said:

There are few key points on how to choose the best packers and movers in ; if you look at those it will definitely serve your purpose. Let us discuss some major points in selecting the best one More information:

August 8, 2018 5:07 AM

Mausam Roy said:

You can take the assistance from renowned packers and movers in  to make you’re shifting an easy to go process. But the challenge you will face is to find the best one upon you can fully trust and rely More information:

August 8, 2018 5:07 AM

Mausam Roy said:

You can search online with the help of a search engine about the packers and movers near you. The search engine will display your numbers of results, so go for the top names and visit their website and find company’s full name, address, email and phone number, etc More information:

August 8, 2018 5:07 AM

Mausam Roy said:

look at their website about the recognition and awards they have received for their performance in this field. Also, check that for how long the company in serving in this industry More information:

August 8, 2018 5:08 AM

Rkman Tiwari said:

August 8, 2018 5:29 AM

Rkman Tiwari said:

it is for you only and we will be happy to support you for the same. Whatever plenty of your it might be, reach us out and explain your condition by calling us at our QuickBooks variety, as we comprehend that your persistence is precious. In conclusion, we cope with sorting the customer’s system issues and thus keeping the business systematic and to be able.

August 8, 2018 5:31 AM

Rkman Tiwari said:

In short, you can use Speed up for creating smarter economical decisions. You can adhere to a strict cost variety and create simpler and never leave your cost variety to any chances.

August 8, 2018 5:33 AM

Rkman Tiwari said:

payables, receivables etc. You can cope with all the economical problems even before they take a form of big problems. Sage 300c assures extensive bookkeeping along with the economical management, like information payable, typical stability sheet, managing of costs, a / r etc.

August 8, 2018 5:34 AM

Rkman Tiwari said:

calls from clients who are experiencing problems while operating with Microsof organization Viewpoint

August 8, 2018 5:34 AM

Rkman Tiwari said:

The AOL High-speed on the internet reaches your house through an ADSL or a wire. The wire mode is mostly underground wiring which brings

August 8, 2018 5:35 AM

clash of clans hilw said:

thanks for post, its great. clash of clans hile , clash of clans hileleri

August 10, 2018 6:46 AM

chenjinyan said:


August 22, 2018 11:18 PM

Karlaner said:

Aw, this was a really nice post. In concept I want to put in writing like this additionally ?taking time and precise effort to make an excellent article?but what can I say?I procrastinate alot and not at all seem to get something done.

September 3, 2018 2:03 AM

chenlixiang said:



September 18, 2018 10:16 PM

Leave a Comment


This Blog


Privacy Statement