Hi All,

I'm trying to automate the "Maintenance plan". I was able to do it through GUI by logging into SQL studio.

But, I'm supposed to automate by embedding the SQL command into jython.

Would be greatfull if somebody can provide me complete steps for maintenance plan.

Below is the Maintenance plan I'm expecting for:

DBE Full Backup
1. Schedule the job to run every night at 5 AM.
2. Configure the tasks in the following order: Back Up Database (Full),
Maintenance Cleanup Task (3 days) and Clean Up History (older than 4
weeks).

DBE Integrity Checks
1. Schedule the job to run every Saturday night at 6 AM.
2. Configure the tasks in the following order: Check Database Integrity and
Update Statistics.

DBE Optimization
1. Schedule the job to run every Saturday night at 10 PM.
2. Configure the tasks in the following order: Rebuild Index.

Hi, I suggest the Ola Hallengren solution

https://ola.hallengren.com/

This solution gives you all the steps you need

hope will help

Yeah, I had gone through this link. 

I'm not an SQL DBA, I'm unable to understand the command given there.

Can you please provide me a sample command and steps to execute them and where to execute. Lets take the above example (Integrity check should happen in following order : 1st should be Integrity check and 2nd should be Update statics) 

I hope I'm clear now!

Thanks!

I'm trying to automate the "Maintenance plan". I was able to do it through GUI by logging into SQL studio.

Hello,

Why don't you use SQL Server Agent Jobs to schedule the maintenance Jobs? You can define the schedule within the maintenance plan using SSMS GUI.

See Create a Maintenance Plan (Maintenance Plan Design Surface) => "Subplan"

I want to automate the task and there shouldn't be any manual intervention. I have automated the creation of database.

Once database is created, I have to run a script which will create a maintenance plan.

Once we confirm the completion, SQL admins will login.

Then script out the jobs and learn the schema so you can migrate the output script into something that can be automated.  Which means, for those tables that use keys and identifiers that are generated at creation time, you need to retrieve them and use them in any following statements.  This isn't rocket science.

• Proposed as answer by Saeid HasaniModerator 6 hours 42 minutes ago

SQL admins will login.

So what exactly do these "admins" actually do? Perhaps the admins want to perform maintenance activities on a realistic schedule and not according to your "automation".  There is a reason you employ administrators.

Then script out the jobs and learn the schema so you can migrate the output script into something that can be automated.  Which means, for those tables that use keys and identifiers that are generated at creation time, you need to retrieve them and use them in any following statements.  This isn't rocket science.

• Proposed as answer by Saeid HasaniModerator Wednesday, April 01, 2015 12:23 AM

Once DB is created, we perform post-installation activity, which includes creating maintenance plan. After which we release the server into production. That's when "SQL DBA's" come into picture.

I have done some research and finally got something, please help me in refining the below:

USE master;
GO
EXEC dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'E:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

GO
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

GO
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

GO
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Once DB is created, we perform post-installation activity, which includes creating maintenance plan. After which we release the server into production. That's when "SQL DBA's" come into picture.

I have done some research and finally got something, please help me in refining the below:

USE master;
GO
EXEC dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'E:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

GO
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

GO
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

GO
EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

the last two steps are redundant.. you do not both of them..just have one of them..