Integrity Check fails

CityOfOrlando · Post by **CityOfOrlando** » Tue Sep 26, 2006 8:03 am

First off, I'm not a SQL Administrator (even though I play one on TV). My SQL Admin has informed me that our SGVault database is not backing up normally because it fails the DB Integrity Check. He has to manually back it up. He then told me that this happens with some vendors and to find out from the vendor if there are known issues with SGVault and SQL Server Integrity checks. What should I tell him?

Post by **jclausius** » Tue Sep 26, 2006 8:13 am

We'll need the info from your SQL Admin.

What is the error message?

What version of SQL Server?

What Service Pack of SQL Server?

How is the backup being created?

Instead of manually kicking off the backup, have you created a job to run the "BACKUP DATABASE" command?

Note - if you are on Service Pack 3 of SQL Server 2000, there is a bug in Microsoft's maintenance routine for backups. This is fixed in Service Pack 4 as Microsoft added a "-SupportComputedColumn" flag to their maintenance plan which is required for the sgdragnet and sgvault databases.

CityOfOrlando · Post by **CityOfOrlando** » Tue Sep 26, 2006 9:13 am

Thanks for the quick response. I've forwarded your questions to the SQL Admin and I'll follow up as soon as possible.

ian_sg · Post by **ian_sg** » Wed Sep 27, 2006 11:01 am

Also note that if you're using the Enterprise Manager wizard to create your backup job, that's known to not work because we're using computed columns. There's info on how to work around that in this knowledge base post: http://support.sourcegear.com/viewtopic.php?t=2924.

Post by **jclausius** » Wed Sep 27, 2006 12:00 pm

ian_sg wrote:Also note that if you're using the Enterprise Manager wizard to create your backup job, that's known to not work because we're using computed columns. There's info on how to work around that in this knowledge base post: http://support.sourcegear.com/viewtopic.php?t=2924.

Note - this is for SQL Server 2000 SP3 and below.

Microsoft fixed this by adding a "-SupportComputedColumn" flag in SP4 for Maintenance Plans created by Enterprise Manager. See my note above.

CityOfOrlando · Post by **CityOfOrlando** » Thu Sep 28, 2006 8:46 am

To answer your earlier questions:

What is the error message?
BACKUP failed to complete the command BACKUP DATABASE [sgvault] TO [sgvault_08_24_06_backup] WITH NOINIT , NOUNLOAD , NAME = N'sgvault backup', NOSKIP , STATS = 10, NOFORMAT

What version of SQL Server?
SQL2000

What Service Pack of SQL Server?
3a

How is the backup being created?
Both manually and job run

Post by **jclausius** » Thu Sep 28, 2006 9:27 am

CityOfOrlando wrote:BACKUP failed to complete the command BACKUP DATABASE [sgvault] TO [sgvault_08_24_06_backup] WITH NOINIT , NOUNLOAD , NAME = N'sgvault backup', NOSKIP , STATS = 10, NOFORMAT

Are there additional supporting errors? This error just shows the backup command.

CityOfOrlando wrote:How is the backup being created?
Both manually and job run

There doesn't seem to be anything wrong with the command. I did notice the NOINIT option. Just to make sure your SQL Admin knows, Vault ships in SIMPLE recovery mode. You may want to alter this based on your backup strategy.