Sql-server – Backup db by using sqlmaint.exe: -CkDB -BkUpOnlyIfClean

sql server

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.

Best Answer

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