Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Quick Note About Backup File Extensions
Published: Dec 05, 2020
This was something I was asked about the other day which I’d not really given any thought to but which was causing others a great deal of confusion…

We’re all aware that the default file type for a SQL Server backup file is “.bak”, but does it have to be?

Well the answer is no. It can actually be anything you like. SQL Server doesn’t care what the file extension is as long as the backup file itself is correctly formatted.

Here’s a quick example using SSMS (for some reason I was in a “pointy clicky” mood today):


As you can see, you can simply set the file name to be anything you like… once run:



And there you have it. Obviously you can restore in the same way as well, with no need to change the file extension (although you can if you wish). Note in the below that we have to change the file types that SQL Server is looking for:



And there it is:


Simply select and restore as normal.

Now… here’s the million dollar question… “Why would you ever want to do that?”.

Well to be honest I use the method myself and the reason is simple… sometimes there is only one network drive in which we place backups for our servers and we don’t like keeping our backups on an infinite basis, so we run maintenance across the drive. In nearly all cases this would involve something, whether it be SSMS, DOS, Powershell etc, running this type of logic: “Delete all files where age > X and file extension = ‘bak’”. Therefore what happens if you actually DO need to take a backup that you wish to keep a little longer than usual? If you take a BAK file then you’re at the mercy of the cleanup, which you don’t want to tamper with… so why not use another extension that won’t get picked up?

That way you can have one cleanup task for your day to day backups and a separate one for any ad-hoc backups you may need to take as needs dictate.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.