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.
