February 7, 2012 at 11:40 am
Hi everyone,
When I launch a backup command in a T-SQL Script (included in a maintenance plan), what can I do in the code to validate that the backup is successfull?
N.B. I use the EXECUTE('BACKUP ... ') command because the name of the database to backup is dynamic.
Do I have to look in msdb.dbo.backupset or other related system tables for the state of this backup? Can I just test with the @@error after issuing the EXECUTE ('BACKUP ... ') command? Or should I rely completely on the red arrow (on failure) included in the maintenance plan interface?
What are the best practices?
Best regards.
Carl
February 7, 2012 at 12:28 pm
Just to get a clear picture, what is that you are trying to do from Maintenance Plan?
You can set the flow of a maintenance plan in such a way that on error(s) so and so action needs to be done (like send an email alert in case of failures etc)..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 7, 2012 at 12:54 pm
Hi Bru,
I will call a remote stored procedure, using a linked server. This procedure will give me the database name to backup.
Than I will do a execute ('BACKUP DATABASE '+@name...').
This will be done every two minutes (within a scheduled maintenance plan).
Thank's a lot.
Carl
February 8, 2012 at 6:08 am
Wait, you're running a full backup every two minutes? Is that right?
Assuming I'm reading that correctly, don't do that. If you want to be able to restore to a point in time, then you should place the database in Full recovery mode and backup the log every two minutes.
As far as validating the backup, here is an article I wrote[/url] on exactly that topic. Basically, for a remote backup like this, add CHECKSUM to the backup process and use RESTORE VERIFY_ONLY. That won't provide you with absolute certainty that the backup is good (you still need to have a consistency check on the database and a restore is the only 100% certain verification), but it'll get you a good part of the way there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 8, 2012 at 6:36 am
Hello Grant,
No, I won't do a full backup of the same database every 2 minutes.
I will check every two minutes if there is a database to backup. 99.9% of the time, a database will be backed up only once.
It is part of a bigger mechanism that allow us to copy databases (backup/restore in a new database) to have, for specific database, one database for each Team Foundation Server's build (in development).
Thank's a lot.
Carl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply