Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using SQL Server 2005 sqlmaint.exe to backup remote sql server databases. Normally I have set up the backup with -CkDB -BkUpOnlyIfClean options.

sqlmaint.exe -S IPForRemote\SLQInstance -U user -P pwd -D db -CkDB -BkUpOnlyIfClean 
   -Rpt C:\LocalPath\MSSQL_Backup\Log\dbbackup.log -VrfyBackup -BkUpMedia DISK 
   -BkUpDB C:\RemotePath\MSSQL_Backup -DelBkUps 3days -DelTxtRpt 3weeks 
   -HtmlRpt C:\LocalPath\MSSQL_Backup\HTMLRpt\dbbackup.html -DelHtmlRpt -3weeks

This one works fine form most of SQL server databases. Today I tried to do the same back against to another SQL Server Express database. I could not do the backup with these options set. The backup fails at the first step:

[1] Database ctc_config: Check Data and Index Linkage...

The following is the error message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5030: [Microsoft][ODBC SQL Server Driver][SQL Server]The database could not be exclusively locked to perform the operation. [Microsoft][ODBC SQL Server Driver][SQL Server]Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

If I don't include them, the back is fine. The database is very similar to the databases I have done before.

I also tried to use SQL Management Studio to do the back on that remote PC, by invoking db's context menu: task->backup.... It works fine. I guess the backup from studio may not include the checking.

I am not sure if the database has some issues or not. I really would like to backup the db with integrity checking.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

I'd recommend moving away from sqlmaint.exe (will get deprecated) and move to SQLCMD in combination with actual SQL queries http://msdn.microsoft.com/en-us/library/ms180944.aspx

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

I have scheduled DBCC CHECKDB as scheduled jobs weekly, you shouldn't need to check it every time you back up

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.