January 20, 2010 at 9:17 am
We have a lot of Servers and I need to find the retention backup value(1 days,etc) from a remote monitoring server.
I was expecting to find that values in a system table or view, but I can't find it.
Can someone shed some light on this? for sure that value is stored somewhere.
Fred
January 20, 2010 at 9:51 am
It would depend on how your backup jobs are created. If they were wizard created as a maintenance plan the value is not in a column by itself. You can query msdb.dbo.sysjobsteps and parse through the command column and pull out the value
-DelBkUps 2DAYS -BkExt "TRN"
.
I cannot come across a server where I have actually just created a SQL Agent job manually and used the master.dbo.xp_delete_file to clean my backups out. I believe though it would be in the same column and table as mentioned, you would just look for the command used to delete files.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 20, 2010 at 10:06 am
Yes I am using the maintenance plan wizard to create it, but when I look in that system catalog this is what I have for command...
/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\test-retention" /set "\Package\Subplan_1.Disable;false"
I don't see the retention value in there.
Note: I'm using SQL 2005
January 20, 2010 at 10:17 am
Yes...I did bring up the wrong instance to test on...at least now you know how to do it on SQL Server 2000 🙂
Hold on let me find it on a 2005 instance.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 20, 2010 at 10:45 am
Well best I can tell that information is not available from a query. Since it created as a SSIS package the contents of that package are in the MSDB store under Integration Services. So the only thing known by the system tables is the location of the package, but not the content details.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 20, 2010 at 11:22 am
many thanks Melton.
On the same note, by searching around I just found that the maintenence plan "history"
is showing the retention...is THAT stored in a system table?
Fred
January 20, 2010 at 11:24 am
GOT IT!!!!! 😀
dbo.sysmaintplan_logdetail
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply