September 26, 2011 at 4:38 am
Hello all,
I have a weekly backup scheduled as a job in SSMS. Restoring to a week-old backup is acceptable given the purpose and design of the database. However I want to avoid a potential situation where database corruption occurs and a backup is peformed before it is detected, leaving me with both a corrupted database and a corrupted backup. The code I use in the scheduled backup is below. Suggestions on how I could modify it to incorporate the following would be much appreciated:
-- Run CHECKDB prior to the backup
-- If errors are found (this is the bit I don't know how to write T-SQL for), email the admin (I know how to write T-SQL for this) and finish the job without performing the backup.
-- If no errors are found, go ahead with the backup
-- Email the admin to notify them if the backup operation completed successfully or not.
I'm guessing that I'll need a CASE or IF clause but I don't know how to capture the outcome of the CHECKDB and BACKUP operations within that.
The code I am currently using for the backup is as follows:
BACKUP DATABASE [Worldscope] TO DISK = N'E:\MyDBBackup'
WITH NOFORMAT, INIT, NAME = N'MyDBBackup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MyDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\MyDBBackup' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
September 26, 2011 at 5:27 am
What I do here is have 1 job for each process of the beackup process.
If checkdb fails I disable the delete old backups jobs and 1-2 others.
That way you keep taking backups (better to have a backup of bad data than nothing).
The other way I've coded this is to throw and error if checkdb() fails. And on error goto error handling in the job steps.
September 26, 2011 at 5:45 am
Isn't it possible that at the end of the "DBCC CHECKDB" step you would choose "On failure action" as "Quit the job reporting failure" and all of the next steps would be ignored since the job is terminated?
Or do the errors from DBCC CHECKDB not trigger the "on failure" action?
I haven't tried this yet so I'm asking as well. 🙂
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 26, 2011 at 5:48 am
codebyo (9/26/2011)
Isn't it possible that at the end of the "DBCC CHECKDB" step you would choose "On failure action" as "Quit the job reporting failure" and all of the next steps would be ignored since the job is terminated?Or do the errors from DBCC CHECKDB not trigger the "on failure" action?
I haven't tried this yet so I'm asking as well. 🙂
Best regards,
I've not tested that way either. What I do is save the output (only errors) and if anything is inserted into the logging table then I assume something is corrupted & take appropriate actions.
September 26, 2011 at 7:03 am
Ninja's_RGR'us (9/26/2011)I've not tested that way either. What I do is save the output (only errors) and if anything is inserted into the logging table then I assume something is corrupted & take appropriate actions.
Indeed.
It's probably safer to do as you say and disable the backup jobs once errors are found.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 26, 2011 at 7:07 am
codebyo (9/26/2011)
Ninja's_RGR'us (9/26/2011)I've not tested that way either. What I do is save the output (only errors) and if anything is inserted into the logging table then I assume something is corrupted & take appropriate actions.
Indeed.
It's probably safer to do as you say and disable the backup jobs once errors are found.
Best regards,
NO, I keep doing the backups. I stop the DELETE backup jobs.
Since I also do daily test retores I know I have valid ones + all the log backups so I want to make sure I don't delete those.
In my system, everything else still runs as if everything is ok.
September 26, 2011 at 7:15 am
I've not tested that way either. What I do is save the output (only errors) and if anything is inserted into the logging table then I assume something is corrupted & take appropriate actions.
Many thanks for the replies folks, very helpful. Just to check that I understand you correctly - do you do this by using @@ERROR directly after the CHECKDB? If not how do you save the errors in the output to your logging table?
September 26, 2011 at 7:18 am
INSERT INTO msdb.dbo.DBA_CheckDB
EXEC ('DBCC CHECKDB(''master'') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS')
UPDATE msdb.dbo.DBA_CheckDB SET DbName = 'master' WHERE DbName IS NULL
IF @@ROWCOUNT <> 0 --corruption in DB, disable jobs, warn admins
BEGIN
EXEC msdb.dbo.sp_update_job @job_name=N'Delete old backups', @enabled=0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'JOB SQL',
@recipients = 'email list',
@query = 'SET NOCOUNT ON; SELECT * FROM msdb.dbo.DBA_CheckDB' ,
@subject = 'ERREUR FATALE, CORRUPTION DE LA BD master',
@attach_query_result_as_file = 1,
@body = 'Voici la query qui réaffiche les résultats de CHECKDB : SELECT * FROM msdb.dbo.DBA_CheckDB',
@importance = 'HIGH',
@query_result_separator = '|',
@file_attachments = '\\Fordiavcenter\Informatique\Navision backup\Help, my database is corrupt_ Now what - SQLServerCentral.mht' -- nvarchar(max);
END
CREATE TABLE [dbo].[DBA_CheckDB](
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [nvarchar](2048) NULL,
[RepairLevel] [nvarchar](22) NULL,
[Status] [int] NULL,
[DbId] [smallint] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [smallint] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefFile] [smallint] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [smallint] NULL,
[DbName] [sysname] NULL
) ON [PRIMARY]
September 26, 2011 at 7:39 am
Excellent! Thank you Ninja's_RGR'us.
September 26, 2011 at 8:07 am
Ninja's_RGR'us (9/26/2011)
codebyo (9/26/2011)
Ninja's_RGR'us (9/26/2011)I've not tested that way either. What I do is save the output (only errors) and if anything is inserted into the logging table then I assume something is corrupted & take appropriate actions.
Indeed.
It's probably safer to do as you say and disable the backup jobs once errors are found.
Best regards,
NO, I keep doing the backups. I stop the DELETE backup jobs.
Since I also do daily test retores I know I have valid ones + all the log backups so I want to make sure I don't delete those.
In my system, everything else still runs as if everything is ok.
Sorry, that's what I meant but I missed the word DELETE as I typed. :blush:
That'd be reckless otherwise. 🙂
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 26, 2011 at 8:17 am
No harm done. I just wanted to make sure that this point was 100% clear :-D.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply