Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Administration (2000)  Verifying Backups in a Scheduled JobStep

Author  Topic  Jay99 468 Posts Posted - 2001-04-17 : 14:53:57 I have a job that runs each night at midnight that backups up the system databases. Step 1 . . . BACKUP DATABASE Master TO MasterBackup Step 2 . . . BACKUP DATABASE Model TO ModelBackup Step 3 . . . BACKUP DATABASE MSDB TO MSDBBackup Now, I know you can run . . . RESTORE VERITYONLY FROM MasterBackup . . . to see that the backup set is complete and that all volumes are readable. I would like to create a jobstep to run the RESTORE VERIFYONLY after each of the backups, but the RESTORE command does not return a value on success of failure. So it seems that all I can do is . . . EXEC msdb..sp_add_jobstep @job_name = 'System Backup', @step_id = 2, @step_name = 'Master Verification', @subsystem = 'TSQL', @command = 'RESTORE VERIFYONLY Master FROM MasterBackup', @on_success_action = 3 @on_fail_action = 3, @database_name = 'Master', @output_file_name = 'f:\mssql7\backup\master\out.txt', @flags = 2 . . . and then each day, go check the out.txt and make sure it sayz 'The backup set is valid.' Is this all I can do? Or is there a way to build in logic to handle (ie, go back to step one) a non-complete or unreadable backup attempt? Thanks Jay