Script to add database to maintenance plan backup

1

I would like to find out how to add new database to my backup maintenance plan using T-SQL.
in SQL 2014

Thanks!

2

That depends on what kind of maintenance plan you are using. If you are using a third party script such as Olla Hallegren's or Minion scripts, they have instructions on how to add/remove etc.

If you are using the maintenance plans built into SQL Server, the maintenance plans are stored as SSIS packages in MSDB server. I don't know of a way to modify that using T-SQL, although there might be. If you want to modify using the user interface, in SSMS object explorer, right click on the maintenance plan under Management -> Maintenance plans.

3

Hi JamesK,

Since you mention to me there is a SSIS package created, then I exported that to a DTSX file and then I will edit the file like working with a XLM file and will simply add a tag for the new database I want to add to the maintenance plan package. Then I will save it and I will schedule my SQL job task to run that DTSX file instead of SQL SSIS.

<SQLTask:SelectedDatabases SQLTask:DatabaseName="New Database" />

Thanks for your help!

4

For reasons that I can't/don't know how to explain, I don't like that approach :smile:

Isn't it just as easy to right-click on the existing Maintenance Plan in SSMS and select Modify and add the new database to the list of selected databases?

5

I understand what you mean. I did not explain the whole thing.

We are developing a web application that looks at SQL backup task as well as other SQL things like the SQL jobs duration, status, etc. We don't want to miss anything.
It displays in a grid view, the databases backed up and those that are not in the backup list.
From a button click, we exclude the database we don't want to back up and from another button click we add the database to the backup list. So it is going to be a one place stop to manage our SQL stuff without the need to have a SQL client installed. Being web, we will be able to manage it from any device having an internet connection and a web browser.
Hope you better understand where we are going with that.

Thanks!