Avatar of Member_2_4371340
Member_2_4371340Flag for Israel asked on

When I go to restore a remote SQL Server 2005 database using Backup Exec 11d, the backed up database file is way too small.

I have 2 Windows 2003 servers running SQL Server 2005 Std. I have BackupExec 11d installed on Server1 of the servers, and have purchased a remote BackupExec agent (for Server2) and an SQL backup plugin for BackupExec.

I run a full Backup of all data every night. If I go to restore any of the client databases, the file size is way too small - I have 2 very large databases, both over 200GB, yet when I set up a restore job in BackupExec, it sees these databases as approximately 2GB in size. I have the MS SQL backup job properties set to "Full, Backup entire database or filegroup". The backup jobs complete successfully every night.

I tried running a restore in the hope that database will amazing expand to it's proper disk size, but no luck. Is it possible that BackupExec has compressed the database file when backing it up? This is seriously worrying me that BackupExec is inexplicably not backing up our client databases...
Storage SoftwareMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Member_2_4371340

8/22/2022 - Mon
rindi

Did you check the data in the database you restored? Is it all there?

Databases very often have a lot of empty, unused space, and is therefore bloated. When you delete data from a database, the database often doesn't get smaller in size. That empty space is usually recovered with backups and restores, making the database smaller and manageable, without you loosing data.
ASKER
Member_2_4371340

Is there any way of checking how much used/unused space is in an MS SQL 2005 database?
rindi

I'm not an SQL person, but there should be tools available that allow you to maintain the database. You can also dump the database into separate files, you could compare the dumps of the original and of the restored database.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
honmapog

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1313431,00.html describes a stored procedure that shows you how much free space there is in every table.
ASKER
Member_2_4371340

I had already checked out that procedure but I was looking for an overall figure of free space in the MDF file. That said, a 240GB MDF backed up to an equivalent file size of 2GB points out that it's impossible that free space in the database is not related to this issue - There's a major Backupexec 11d bug causing this error.
ASKER CERTIFIED SOLUTION
Member_2_4371340

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
honmapog

Are you using software compression? That would solve the "mistery". SQL databases and log files are indeed highly compressible. If you use software compression, Backup Exec will probably report the compressed size.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Member_2_4371340

No, I'm not using compression. Compression of any kind of database wouldn't allow for 2500% size difference!!! When I back up using MS SQL's backup utility, the database stays roughly the same size. The size of file I was getting from Backup Exec was ~2GB instead 246GB, yet the software is telling me that the backup completed successfully... Somethings amiss, and it might be something to do with locking large MDF files, or the remote SQL backup agent...