How to backup a database only it passes an integrity check using Expressmaint
I got a question recently asking if it is possible using the Expressmaint utility to perform an integrity check of a database and then do a full backup only if there were no errors reported from the integrity check. Although there is no built in functionality for this, it's very easy to automate using a simple batch file as demonstrated below
:: Check Integrity and only backup if ok
@echo off
SET DBNAME=master
SET INSTANCE=(local)\SQLExpress
SET BACKUPFOLDER=C:\backups
SET REPORTFOLDER=C:\reports
expressmaint -S %INSTANCE% -D %DBNAME% -T CHECKDB -R %REPORTFOLDER% -RU DAYS -RV 7
IF %ERRORLEVEL% EQU 0 (
expressmaint -S %INSTANCE% -D %DBNAME% -T DB -B %BACKUPFOLDER% -BU DAYS -BV 2 -R %REPORTFOLDER% -RU DAYS -RV 7
) ELSE (
echo CheckDB finished unsuccessfully so no backup done
)
As you can see, we run an integrity check and only if the return code is 0 (i.e. no errors reported) is the full backup performed. Using simple batch files or scripting, it's easy to customise how the utility works to suit your environment. And if you're feeling adventurous, you can always download the source code from Codeplex and customise it yourself!