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...
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...
ASKER
Is there any way of checking how much used/unused space is in an MS SQL 2005 database?
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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...
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.