I was wondering if it is possible to tell the difference between backups created manually and backups created through a maintenance plan.

There does not seem to be any way to discriminate them in the msdb backup related tables apart from the account that the Sql Agent runs under is placed in the user_name column in the msdb..backupset table

I would like to verify that backups done through a maintenance plan exist on disk but don't really care about any manually created ones. Part of the plan compresses and copies the archives to another server. These will be compared to make sure everything is ok with the plan.

  • 1
    Well, you should care about manually created ones. What if they broke your log chain? :-) – Aaron Bertrand Feb 14 '12 at 2:27

When you set up a backup using the Maintenance plan, it provides a rather complex name for the database backup (example: MovieManagement_backup_2012_02_14_064551_7520824). You can view this by using RESTORE HEADERONLY. Most people don't supply any name at all when running a backup manually. This can be the difference. Other than that, no, I'm not so sure there's anything you can do there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.