Tag Archives: Database Backups

What should be the File Extension of SQL Server Backups and why?

Posted on by
3

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

SQL Server Database Backup types and why are they required

Posted on by
Reply

Database Backups are very important and there is not much to say why are they important; that is the only way to restore the data back to a point of time. In this blog we shall learn the different database backup types available in SQL Server. There are 3 basic types of backups (there are other types too) in SQL Server which every aspiring SQL Server Database Admin should be aware. They are

  • Full
  • Differential  and
  • Transaction Log

Let us look at more details about these backup types.

Full Backup: Full backup is perhaps the most basic type of the backup in SQL Server and the important too. Full backup contains the complete database, including the data (in the tables) and other database objects such as Indexes, Stored Procs, Triggers etc.. Using this backup, the database can be restored (or recovered) until the point-in-time when the Full backup was completed. The advantage with Full backups is that they are completely independent of any other backups, unlike the other 2 types of backups. Since a Full backup will contain the entire database, the size of the backup will be proportionate to the actual backup size (when the backup is not compressed, a feature released in SQL Server 2008).  The same is true with the amount of time it takes to perform a Full backup, the larger the database, greater the time. Any kind of database restore should always begin with a Full backup. These backups can be taken on a database with any of the recovery model.

Differential Backup: Differential backups contain all database changes made since the last Full backup. A differential backup taken a day after the Full backup will contain all the database changes during that one day. A differential backup taken 2 days after the Full backup will contain all the changes during those 2 days. Due to this, Differential backups can be called as cumulative in nature. A recent differential backup contains the changes that were already backed up by a previous differential backup. As more and more data is modified, the Differential backups size increases. A Differential backup cannot be restored separately, instead it should always be used along with a good Full backup set (that is, the most recent Full backup preceding the Differential backup). Differential backups can be taken on a database with any of the recovery model.

Transaction Log: Transaction Log backup also called as Log backup contains all the changes that have been made since the last Full backup or a previous Log backup. Transaction log backups are opposite to Differential backups, i.e, you need each and every transaction log backup, before a specific point-in-time. Maintaining and protecting all transaction log backups is vital. A single log backup missing in a chain can break the ability to restore the database to the most recent point-in-time. Unlike Full and Differential backups, the benefit of Transaction log backups is that they can help us restore to a specific point in time. Log backups are generally scheduled to run at a meaningful short interval such as every 10 or 15 minutes. Log backups are available for only those database which have the recovery model set to Full or Bulk-Logged. 

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