Link to home
Start Free TrialLog in
Avatar of ITMokra
ITMokra

asked on

How to create MS SQL Maintenance Plan Programmatically?

Hi,

does anyone have a short example of code (let's say in C#) which can generate Maintenance Plan (could be SSIS package).
We have hundreds SQL Servers in our scope, and it would be much easier for us to automate deploy and backup of them. We do not want to use specialized tools like Quest LiteSpeed etc. But we would prefere to create C# command line program which will allow us to easily create Maintenance Plan (or SSIS package) which will be automatically deployed to target SQL 2005, 2008, 2008R2 Server and will backup and do basic maintenance tasks upon all online databases which are there. We have found some examples on : http://technet.microsoft.com/en-us/library/ms345167.aspx, but more related to SSIS packages than SQL Maintance Plans.

Any help will be widely appreciated.

Thanks

Avatar of lcohan
lcohan
Flag of Canada image

Here's one option - create your SSIS generic maintenance package for all your servers manually and use a config file for it then deploy to each of your SQL servers/computers

SSIS Package Configurations: http://msdn.microsoft.com/en-us/library/ms141682.aspx

How to: Deploy a Package: http://msdn.microsoft.com/en-us/library/ms140117.aspx
Avatar of ITMokra
ITMokra

ASKER

Seems like good idea, will try that.
Do you think it will work when deployed again to different SQL Server?
Avatar of ITMokra

ASKER

Or is it possible to create maintenance plans programmatically by using Microsoft.SqlServer.Management.DatabaseMaintenance namespace or am I wrong?

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.databasemaintenance(SQL.100).aspx

Thanks
Avatar of ITMokra

ASKER

Seems the namespace is working, successfully created Empty Maintenance Plan.

private void btnCreatePackage_Click(object sender, EventArgs e)
        {
            // Create server Connection Microsoft.SqlServer.Management.Common;
            ServerConnection conn = new ServerConnection("SQLServerName");
           
            // Create maintenance plan object with connection
            MaintenancePlan myMaintenance = new MaintenancePlan(conn);
            // Setup Maintenance plan properties                        
            myMaintenance.Description = "Backups";
            myMaintenance.Name = "SDOMOM BackupAllUsersDBs_Test";
            myMaintenance.BasePackage.Configurations.Add();
           
            /*
            //Create backup task
            DbMaintenanceBackupTask bckFull = new DbMaintenanceBackupTask();
            bckFull.BackupAction = Microsoft.SqlServer.Management.Smo.BackupActionType.Database;
            bckFull.BackupCompressionOption = Microsoft.SqlServer.Management.Smo.BackupCompressionOptions.Default;
            bckFull.BackupIsIncremental = false;
            bckFull.BackupDeviceType = Microsoft.SqlServer.Management.Smo.DeviceType.File;
            bckFull.BackupFileExtension = "bak";
            bckFull.DatabaseSelectionType = DatabaseSelection.User;
            bckFull.DestinationAutoFolderPath = @"C:\Temp\";
            bckFull.IgnoreDatabasesInNotOnlineState = true;
            bckFull.InDays = true;
            bckFull.DestinationCreationType = DestinationType.Auto;
            bckFull.ExistingBackupsAction = ActionForExistingBackups.Append;
            bckFull.LocalConnectionForLogging = "Local server connection";
            bckFull.TaskName = "Back Up Database (Full)";
            bckFull.CanUpdate("Back Up Database (Full)");
           
           
             */
            // Save Maintenance Plan
            myMaintenance.Save();
       }

Now I'm trying to create Backup Task, (should be subplan or sequence first, probably).
"Do you think it will work when deployed again to different SQL Server? " - they must work 100% if designed/built to read the connection and other server/database speciffic parameters from config files that will be unique for each server.
Avatar of ITMokra

ASKER

I will try both ways because it seems that Microsoft.SqlServer.Management.DatabaseMaintenance is quite new, and not well documented. Actually I was able to easily create maintenance plan with c# but couldn't create db backup tasks. :-(
ASKER CERTIFIED SOLUTION
Avatar of ITMokra
ITMokra

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ITMokra

ASKER

It was solved exactly how I've expected.
It is completely meets our needs.