August 14, 2018 at 11:25 am
It seems pretty straight forward but I can't find it anywhere .
1. I am trying to locate SQL backup Maintenance plan details via script i.e destination server location , what are the databases selected .
2. If point (1) can be achieved then , I want to update via T-SQL the list of databases or destination location for backup .
Thanks !
August 14, 2018 at 11:52 am
RatanDeep Saha - Tuesday, August 14, 2018 11:25 AMIt seems pretty straight forward but I can't find it anywhere .1. I am trying to locate SQL backup Maintenance plan details via script i.e destination server location , what are the databases selected .
2. If point (1) can be achieved then , I want to update via T-SQL the list of databases or destination location for backup .Thanks !
Maintenance plans are stored as SSIS packages. The definitions are in the packagedata column in the table msdb.dbo.sysssispackages. Packagetype = 6 for maintenance plans.
Personally, I wouldn't update maintenance this way but then again, I don't use them so it doesn't matter what I would do 🙂
You can find some examples of casting the data to xml and updating from there. Search on: update sysssispackages
Here is one example:
How to changes or update local server connection in Maintenance plan job
Sue
August 14, 2018 at 12:37 pm
Found this link , but it still does not gives the details on configuration setting .
August 14, 2018 at 1:58 pm
RatanDeep Saha - Tuesday, August 14, 2018 12:37 PMFound this link , but it still does not gives the details on configuration setting .
You will likely need to figure out your exact needs doing something along the lines of what the other posts show. And look through the XML to find how you need to change things.
Or look at moving to scripts instead of maintenance plans. Maintenance plans are easy to setup. They can be a bit time consuming if you are going to be updating them.
Sue
August 15, 2018 at 11:10 am
If your SQL Server is 2005 or later i would try to get away from maintenance plans and use T-SQL scripts or Ola's stuff(Ola Hallengren)... So much easier to make your way thru it in the long run and gives much better control. i know maintenance plans are used by plenty but no my preferred way.
Good Luck
D
DHeath
August 15, 2018 at 3:30 pm
yup I agree with , I ended doing manually changing the backup location back and worth because of unexpected drive issues , I figured modfiying the ssispackages data would be more painful than doing manual click click and done .
Now I have the quick script to check through my mediafamilyset to check how the backup ran in last couple of nights. I agree having backup tool is so much convenient like Redgates
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply