Video of the week

I've found this great video and I recommend you to watch if you are interested in wireshark and filtering methods

[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?

destination

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):

append

double

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

position

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