finding backup path for maint plans in sys db

Question

finding backup path for maint plans in sys db

Sam Greene

SSCertifiable

Points: 5252
More actions
November 13, 2006 at 4:09 pm

#174945

Hi,
Does anyone know if the paths for the db and trans backup are stored in sql server 2000 anywhere? Of course I've found the sysdbmainplan tables, but it doesn't seem to contain this information. Is it saved in the job tables somewhere? I am trying to get a list of where all our dbs are being backed up by maintenance plans.
Thanks for any help
sam

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 1

Hi Sam,

In Enterprise Manager browse to Management and select Backup. Right click and select Export List. This will give you the option to save all backup devices with their locations to a .csv file.

Cath

RichB SSCrazy Eights Points: 9651 More actions · Answer 2

Or you can query msdb..backupset and the associated tables.

Sam Greene SSCertifiable Points: 5252 More actions · Answer 3

wangkhar,

I don't see a path to disk or any way of linking to a maintenance plan there - the backup_set_uuid doesn't seem to link to plan_id in sysdbmaintplans.

Cath,

Thanks! That cuts out some keying.

Sam

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

Yes, you need to go to backupmediafamily to get the logical and physical paths, but from those you can construct a report detailing the various backups.

How to tie that back to the Maintenance plans I don't know, but then I don't use maintenance plans that much.

Sam Greene SSCertifiable Points: 5252 More actions · Answer 5

Sam Greene

SSCertifiable

Points: 5252

November 14, 2006 at 8:38 am

#671661

I see. Thanks for your assistance.

sam

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Here's a handy little reference on the system tables no DBA should be without:

http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Sam Greene SSCertifiable Points: 5252 More actions · Answer 7

That is a great little ERD! Thanks alot.

Still, I see no link between the job and the backup it creates. I know EM gathers parameters for the sqlmaint.exe command - i'm wondering where it stores these, or does it strip them off of the command it stores with the job. I'm hoping it's not done that way.

thanks again

Sam

Sam Greene SSCertifiable Points: 5252 More actions · Answer 8

I setup a trace on EM and found this bit of code running when you click properties of a maint plan

exec msdb..sp_help_jobstep @job_id = 0x9DDEE741CB1B5142B51549E57E0DB4DD, @step_id = 1

That is executed for each job the plan has created and eventually i get one that returns:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5A2EC128-9AA0-4B6E-91BC-1BFA362FF994 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "d:\BACKUPs" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'

So, it does seem to me if this is all that if this is all em is getting back, then this is how it is stored...they must use regex's to strip out the parameters and populate their control.

RichB SSCrazy Eights Points: 9651 More actions · Answer 9

I wonder if you actaully need to tie it back to the job/maint plan, or if this is something you have sidetracked yourself into? If you do, then you could try using the datetimes of the backup tables and the sysjobhistory to get some correlation, but if you don't then a simple report showing the frequency and types of backup for each backup taken will give you the knowledge of the backups?

Sam Greene SSCertifiable Points: 5252 More actions · Answer 10

I was wondering the same thing - as we only create backups thorough the maintenance plan, we can presume they exist there.

thanks alot

sam

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

All xp_sqlmaint does is call the sqlmaint.exe utility and passed the string to it on the command line. Since sqlmaint.exe is a console application, it handles command line parameters like any other console application can.

Because the directory is being specified in the command line, you guessed it, you aren't going to find it in a column by itself in one of the msdb tables. However, sam's idea about regexing would work because -BkUpDB tells you the starting point and -CrBkSubDir tells you that it will create a separate directory for each database (using the name of the database). Therefore, a simple script can put these together to figure out the directories.

K. Brian Kelley
@kbriankelley