June 10, 2013 at 8:00 pm
Hi, I have a customised maintenance plan that rebuilds the indexes and updates the statistics and then takes a full DB backup.
Is there a way to find out what backup files where created by the maintenance sub-plan?
I used to be able to do this with SQL Server 2000 but with 2008 R2 it does not look easy.
Regards,
Ignacio
June 11, 2013 at 12:13 am
In Maintenance plan, you should be provided a name conventions for your backup files. Other than this, are you expecting a logging mechanism ?
June 11, 2013 at 12:19 am
I would not like to change the backup file names with a prefix name of the maintenance sub-plan. I want to maintain the default file name generated by the plan as standard as possible.
I like to thought of loggin the maintenance plan execution but how to do you tell it to log the backup file name?
I am surprise if no one has asked this question before.
June 11, 2013 at 1:43 am
Query the backup tables in the MSDB database to see a logging of all backups including the files created.
Something like:
SELECT database_name, name, physical_device_name, backup_start_date, type
FROM [msdb].[dbo].[backupmediafamily]
INNER JOIN [msdb].[dbo].[backupset] bkset
ON [backupmediafamily].media_set_id = bkset.media_set_id
Above code could be expanded with the [backupfile], [backupfilegroup] and the [backupmediaset] tables if needed.
June 11, 2013 at 5:03 pm
Hi HanShi, thanks for your reply but I don't think that gives me the link to which Maintenance plan step created a backup.
I'll give you an example. If I have a maintenance plan to rebuild indexes and then take a full backup starting Saturday before midnight and it takes longer than usual and finishes after midnight it would result in two full backups on Sunday, one early morning by the rebuild index and another by the another maintenance plan that thake the nightly backups.
It would be nice to have a relationship between the maintenance plan/sub-plans and the backups it creates (it existed in sql 2000) other wise finding the backup created by the rebuild index step would be a guessing game. Suppose some one run a manual full backup between these jobs, how can you be certain which backup was generated by who? This is just one scenerio, I suppose there could be other combinations that could also result is guessing what plan-step created what.:w00t:
June 12, 2013 at 12:01 am
Hi,
I understand your situation. When you only want to know the backups taken by the maintenance plan, you could query the table [msdb]..[sysmaintplan_logdetail]. This will give you a log-result of every execution of a maintenance job. In the command used is the name of the created backupfile mentioned.
Maybe you could even combine the start_time and end_time columns to match the backups in the [msdb]..[backup%] tables. Then you can find out if other backup are taken without use of the maintenance jobs.
June 12, 2013 at 1:09 am
HanShi, the table [sysmaintplan_logdetail] is what I need. Like you said I can derive the backups taken by the scheduled task IDs.
I had no idea that this table existed. It's confusing when there are other tables with similar names "sysdbmaintpla%" that are obsolete, I gave up too soon when I noticed that they where empty, I should have looked at all '%maint%' tables.
Muchas Gracias.:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply