Search code examples
sql-servercmdsqlcmd

Return value of SQLCMD RESTORE in cmd batch

I am building a cmd script to restore a SQL Server database, and I need to know if the RESTORE worked correctly, in order to perform other tasks. My code:

sqlcmd -S %DATABASE_SERVER% -U user-P password-Q "RESTORE DATABASE %Database% FROM DISK='I:\bakup.bak'"

Thanks

Solution

  • Backup command doesn't return an error code. Moreover, backup error can be found in error log only, not in any of the system catalogs.

    There is a table msdb.dbo.backupset with information on successful backups, though, and it can be used to deduce whether the backup errored or not.

    Make a note of the current time prior to taking the backup, and after the backup finishes use a query like this to retireve the time of the last successful backup:

    select max(backup_start_date)
from msdb.dbo.backupset
where database_name = 'database_name'

    If the time returned is less than the one you recorded then there were errors.