Doing a backup in T-SQL

  • 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

  • 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)..


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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