09-27-2011 11:19 AM
I am running Backup Exec 2010 R3 on my SQL box and ran across something that concerned me. I am backing up the SQL data via an agent and when I select the size in the backup job, it is ~17GB. However, when I select a restore job, the size is much smaller. Like 8GB. Is this correct? I just want to make sure I'm backing up what I think I am.
Thanks!!!
Solved! Go to Solution.
10-03-2011 10:08 AM
Dear Sir:
When Backup Exec uses its SQL Agent to backup and restore SQL databases, it will show the actual size of the data inside the database file and not the size of the database on the disk. This is a known behaviour and you should not worry about it.
Even though the size of the database may appear smaller than what is shown on the disk, if you test the restore you will see that all the data is correct and in a consistent state.
09-28-2011 01:23 AM
Hi there,
What version of SQL are you running? It could be that you are making use of BE's SQL 2008 compression if you are using SQL 2008?
Thanks!
09-29-2011 10:30 AM
Yes 2008 SQL.
09-29-2011 11:10 AM
A database can contain empty spaces. To release this you have to shrink a database:
09-29-2011 04:06 PM
So maybe the restore is showing the real database size, minus whitespace?
09-29-2011 11:48 PM
...just check and make sure you haven't selected option Compress under SQL Server (2008 or later) software compression (Tools --> Options --> Microsoft SQL). If you have, this explains why the sizes are different.
09-30-2011 02:48 PM
It is not set to software conpression in the SQL settings in BE.
10-01-2011 01:10 AM
...and within SQL itself?
Otherwise the only confirmation you will get to see if you;'re backing up the SQL databases correctly and they are recoverable is to do a redirected restore to another SQL server.
10-03-2011 05:46 AM
Where is the setting in SQL itself?
10-03-2011 06:15 AM
...can't help you there. You'd have to check for that yourself. It falls outside of my knowledge of SQL :)
10-03-2011 10:08 AM
Dear Sir:
When Backup Exec uses its SQL Agent to backup and restore SQL databases, it will show the actual size of the data inside the database file and not the size of the database on the disk. This is a known behaviour and you should not worry about it.
Even though the size of the database may appear smaller than what is shown on the disk, if you test the restore you will see that all the data is correct and in a consistent state.