This looks like a simple question, but I had often seen in many forums this question being asked, I happened to answer a question on SQLServerCentral.com as recently as last week. So I thought why not I write a blog post on this.
What should be the extensions for backup files?
One of the answers is, for our convenience to remember what type of backup is stored with what type of extensions. Most of the time I had worked, seen or heard, it was a general industry wide accepted extension of .BAK for Full backups, .DIFF for Differential and .TRN for transactional (Upper case is not a must, only to highlight the file extension). Doing this way, it would be easier across your team (of 2 or 20) to recognize what is the backup type by looking at the file extension.
SQL Server does not have problem with the file extension as long as the file is a valid file. You can name your backup as db_full.zip or . sql or any funny extension as you wish and write the backup information into that file. If the backup was completed successfully, you can use that file to restore without any issues..
Why should this be practiced?
Why it should be named using a certain extensions, the reasons are many. The first reason is to quicken you restores, yes the ultimate goal of a backup is to restore your data in case of a failure (user, hardware or a natural disaster). So when you are trying to restore you database from your backups, you would want to know what kind of backup is that backup file just by looking at the extension. As soon as you see that it is a .diff, you know, ok this is my differential backup file. You would not want to waste time by running a restore command against a backup file and then the see a message that this is not the kind of backup the SQL Server is waiting for..
To give you another reason, usually there are exceptions added in your server Anti Virus software so that as soon as it sees certain files with extensions, such as .bak or .diff or .trn, it would not run a virus check, in order to save the disk read / write overhead and the processor usage on the server.
Finally, what if a backup file saved with extension “.jkl” and due to an unrecognized extension the file was deleted by one of your team members. May be that might be the only backup copy and that was deleted…
Remember this.. Being a DBA is to plan for the most unexpected disaster to happen and still be able to get everything covered..
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.
–Bru Medishetty
Wow… so many misconceptions in here.
“what if a backup file… due to an unrecognized extension the file was deleted”
You have a bigger problem that file extension naming. Backups contain all of your data. If the backup file was restored to a different server, where a “bad actor” was a sysadmin, they now have complete access to all of the data within that database (except for databases where TDE was enabled or where the data was encrypted within the database itself).
No one should have access to delete (or read) backup files, except for those that have a need to do so. Principle of least privilege.
It’s just just a good practice, in cases of regulated industries, it’s required for regulatory compliance.
BAK vs. TRN vs DIFF…. this implies that full backups will go to one file, transaction log backups will go to different file(s) and differential backups will go to still more file(s).
All backups for a database for a given day (or backup rotation) should be stored in a single file. This makes it easier to restore. After all, taking a backup is meaningless, it’s the restore functionality we want to have available.
What do you do if you have a DIFF file, but you don’t have the full backup that it was based on?
What do you do if you have your transaction log backups in each in separate files and you miss one?
In both cases: you lose data.
For SIMPLE recovery model databases, start a cycle with a full backup. From there, take scheduled differential backups, into the same file. Your recovery path: Restore the initial full backup and the last differential. No files to misplace.
For FULL recovery model databases, start a cycle with a full backup. From there, take scheduled transaction log backups into the same file. Your recovery path: Restore the initial full backup and each transaction log backup in order.
Marc,
I am not sure if you have understood the context of the blog…
Regarding your first line… “what if a backup file… due to an unrecognized extension the file was deleted”.
Let me give you a scenario, a Windows Admin on a SQL box is trying to get rid of some files in order to recover disk space and deleted the a file with extension “.jkl”. Whether or not this can happen, it depends.. You never know when it happens.. But it will have to be considered a chance of .01 %. I have a mission critical production database which was never restored to a point-in-time in the last 4 years. So would you say at the end of this year, the chances of this happening is very unlikely and remove log backups and only rely on Full backups?
I am well aware of what would be the impact if a malicious person has your full backups…
Regarding your comment about all backups for a day (or a backup rotation) be stored in a single backup file, I would completely disagree with you, not only me there would be many who would not agree with you on that.
What if that single file is corrupted, now you don’t have anything to restore forget about loosing one file in log backup chain, you are not good to even restore the full backup also..
Also in many cases the backup files are stored in multiple copies (Tape Drives, Network shares), if all of your backups for a single db are stored in 1 file, do you know how hard is it to copy over to tape drives or network shares .. I am talking about databases which are in 1000’s of GB, not in 10 or 100 GB.. Add a full backup of 700 GB, and Differentials of 50, 100, 200 GB as the week goes on and then T-Log backups of 500-600 MBs attached all to that single file and each day / each scheduled interval that file is to be copied across network.. It wont work in my case and many environments..
Finally, I am completely aware of recovery paths and have done multiple times involving Full, Differentials and T-Log backups and done Point-in-Time many times..
–Bru Medishetty
How we Recover Delete Data in Sql Server Plz Help me