Video of the week

This is a must-watch video about one of us trying to reach the stars :-)

Well done #HRejterzy

[SQL SERVER] Backup set

Every time you do a backup of your database in MS SQL Server, there's the following prompt window, which gives you the option to indicate a destination place for the backup. It's located right under the Back up to dropdown option. If I asked you about the true meaning of this option, would you say that this is a simple file? Or maybe you would answer that this is something much more complex? The second version is much more close to the truth. So what is AdventureWorks2012_FULL_DB.bak exactly?


It's not a simple file. This is a backup set which includes all your backups that have been done so far (unless you overwrite the backup set each time you do a backup - let's assume you don't). Every time you backup your db, you simply append a new backup to the previous ones. So it's possible that AdventureWorks2012_FULL_DB.bak consists of more than one backup (stored physically inside the bak file). I just did the first full backup of my AdventureWorks2012 database, let's look at the Backup directory:

full backup

Now let's do another one - let's append another full backup to our backup set and look at the result (especially in terms of the size):



Our backup set includes two backups. Our disk file is as twice as when we did the first full backup.


Now we can see clearly, that our backup set (AdventureWorks2012_FULL_DB.bak) includes two backups, first one is at the Position 1 and the second one is at the Position 2 - both in the same physical file. Don't pay attention to the dates because in the meantime I did additional two backups after removing previous ones, but it had nothing to do with the case. The case is that when we indicate a destination for our backup, we indicate a backup set which is something more complex than a simple file with the bak extension.

backup set