BE 2010 R3 and SQL

[TBone_1985]

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!!!

[CraigV]

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!

[TBone_1985]

Yes 2008 SQL.

[ZeRoC00L]

A database can contain empty spaces. To release this you have to shrink a database:

http://technet.microsoft.com/en-us/library/ms189035.aspx

[TBone_1985]

So maybe the restore is showing the real database size, minus whitespace?

[CraigV]

...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.

[TBone_1985]

It is not set to software conpression in the SQL settings in BE.

[CraigV]

...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.

[TBone_1985]

Where is the setting in SQL itself?

[CraigV]

...can't help you there. You'd have to check for that yourself. It falls outside of my knowledge of SQL :)

[Carlos_Quiroga · Accepted solution]

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.