Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lbseong
Starting Member
5 Posts |
 Posted - 2010-12-09 : 20:50:51
|
| SQL2008 - How to duplicate the Maintenance Plan for other user DBss??Hi, I am currently using SQL2008 and need to create a Maintenance Plan for all user DBs(~ 60 user DBs) with the MP listed below:-1.) TLog backup2.) Rebuilding IndexI would like to check with the expert, is there a faster way for us to export the package, is yes..How? The requirement is we will need to show there are MP showing in the SSMS.(SOME POLICY..@#$%%^^)e.g: MaintenancePlan_TLOG_For_DBName,*** Might be different schedule depend on the userdb(~ from 15min to 6 hours)MaintenancePlan_RebuildIndex_For_DBName*** Need to set different schedule so that it will not taken all the resource in one go These mean, I will have to use wizard to create 120x for 60 UserDBs, this is ....Is there anyone can suggest any fastest way, easy way for me?Thanks in advance |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-12-09 : 21:58:41
|
| You really have to create a separate maintenance plan for each database and task? Are these databases on the same server - or do you have multiple instances?Either way, here is what you can do:1) Open BIDS and create a new SSIS package2) Delete the default Package.dtsx that is created3) Right-click on the Packages folder in the Solution Explorera) Add Existing Itemb) Put in the SQL Server where you plans existc) Select the plan - repeat for each plan4) If moving to another server, open the package in BIDS and change the local connection to the new servera) Note: do not try to add items here - it could break the package. Only modify the tasks to select the database(s) and path information.5) Once those changes have been made, select File | Save Copy Asa) Save a copy to the server where you want the new maintenance plan.b) You will be prompted for the name, select a name that does not already exist6) Once the package has been saved, open the maintenance plan in SSMS and create the schedule.a) This will generate the agent job to run the maintenance plan.Recommended: before editing in SSMS you should update the owner of the maintenance plan in the ssispackages table in the MSDB database. I recommend changing it to sa - this way there won't be any issues with ownership of the plan or with running the agent jobs (e.g. if the owners AD account is no longer active, the agent jobs will fail).Note: do not change the connection method from Windows Authentication to SQL Authentication. There is no reason to make this change and it could cause the maintenance plan to fail.Jeff |
 |
|
|
lbseong
Starting Member
5 Posts |
Posted - 2010-12-10 : 02:12:53
|
| Hi Jeff, Thank you so much for your time replying my question. I have try your suggested method. It doesn't really help to save much time as I will need to perform some other minor task (sometime hits some error).1.)Changing the name for "MaintenancePlan_TLOG_For_DBName" for individual user dbe.g: a.) MaintenancePlan_TLOG_For_DB1, b.) MaintenancePlan_TLOG_For_DB2, b.) MaintenancePlan_TLOG_For_DB....2.) I will still need to use SSMS to open each MP to add in the schedule...Fyi, I will need to perform for the same MP for other Server, Instance as well.*** This is taking more time as I am remotely connect to other region server Not sure is there a simple and faster way that we can do it? I remember SQL2000 Job can just script out and modify then apply...I know Microsoft have limit this to SQL2005 onward...if you still have some other option, please do share with us....thanks...I willl need to deploy all these to all server within a week and the DBs in total are ~ 500 User DB x 2 =1000 MP that need to implement...My nightmare just about to start ... |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-12-10 : 16:07:58
|
| The only options you really have are:1) Use SQL Agent Jobs instead of maintenance plans and script them in2) Don't have a single plan for each database, use a single plan for multiple (all) databasesWhat I do is create generic maintenance plans for a system. I create four maintenance plans called:History CleanupSystem DB MaintUser DB MaintUser TLog MaintThe History Cleanup plan has one sub-plan scheduled daily at 12:01am. The only task in that plan is the History Cleanup task set to cleanup history older than 6 months.The System DB Maint plan has one sub-plan which is scheduled based upon the maintenance window (generally around 2am). In that sub-plan I have 3 tasks: Integrity Checks (all system databases), Backup Databases (all system databases) and Remove Old Backup Files (maintenance cleanup task).The User DB Maint plan has two sub-plans. The daily sub-plan performs integrity checks on all user databases, backups for all user databases and removes old files. The weekly plan rebuilds indexes on all user databases and updates statistics on all user databases. I adjust these plans as needed for each system...The User TLog Maint plan has one or more sub-plans, scheduled for the specific databases and their log backup requirements. Each sub-plan will have only one task - backup the transaction log for the specified databases.Now, I also have another task added to all of my sub-plans - which is the Notify Operator task. This is connected to every other task in the plan on failure and I change the condition from AND to OR (this changes the dependency line to a dashed line).On a new server build, I load my generic plans from by saved BIDS project and adjust as necessary for that system.Your requirement to have separate maintenance plans for each database, and seperate maintenance plans for each task is what is causing all of your issues. If you didn't have that requirement, then it would be easy to setup and configure.Also, you can use the Execute SQL Task in your maintenance plans. I use this when I want to do something that the normal tasks cannot handle. For example, I have mirrored databases - and I need my maintenance plans installed and configured on the mirror system. However, if I try to run the normal tasks - they will fail. So, I will be creating custom code to check the state of each database, and if it is not online - it will be skipped.Jeff |
|
|
|
lbseong
Starting Member
5 Posts |
Posted - 2010-12-10 : 16:26:01
|
| Hi Jeff, Thanks for your advise, I have suggested to have single MP for all User DB, but again it is still pending for approver(no news yet). Hence, for time being, I will need to create all the MP for each DBs...the QA team will generate the report and need to have "prove" of the MP for each db(2 for each). SQL Job will not be allow as well, (it will have to be MP)...that is the problem that causing me a lot of "manual" hard-work...I really wonders what is this policy are...don't we able to change something on this...this is really suck me in a lot of time... I totally agree with your suggestion...but until my management agree ,otherwise we will not allow to hv this single MP... :-(. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
How to duplicate Maintenance Plan for other DB