SQL Server / Backup File Size Issue

Posted by in SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments

Yesterday, one of my SQL alerts just sent me an email about my daily backups size. I noticed that my backup has doubled in size.

What happened is that I changed my Task Scheduler which is running my PowerShell backup script and accidentally ran backup twice for the same backup set.  Nevertheless, SQL Server is smart enough and instead of overwriting your existing backup file its just append the new one. You can see from the picture below that default for SQL server (2012 in this case) is “append to the existing backup set”.

Can be confusing but it is really the safest option for the user.

In this case, it is the same database, but this could happen with backups on multiple databases.

Now, if we check that backup set

RESTORE HEADERONLY FROM DISK = ‘D:\MSSQL\Backup\db02\db02_db_201710090934.bak’;

You can see there are two backups for the same database with different backup start and finish time.  Of course, you can choose which backup you want to restore, and you can use GUI or code

With the previous command restore HEADERONLY you also fetch column named “position”, use this column to determine which file you want to restore.

RESTORE DATABASE DB02 FROM DISK = ‘D:\MSSQL\Backup\db02\db02_db_201710090934.bak‘ WITH FILE = 2, REPLACE

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *