Hi this is Vaibhav Shringi, This is my 3rd article on SQL server 2016, in this article, will explain in detail how to transfer maintenance plan from one server to another.
In the following example, I will use two servers NMK135 and VS12345 for explanation and sample users Vadmin and nmk12345.
Transfer Maintenance Plan from One Server to Another
1. Connect to Source Integration server
Connect to Integration Services in SQL Server
Management Studio (Where Maintenance plan already present Say SERVER-1)
2. Export Maintenance plan package from SERVER-1.
appropriate permissions and export the “DBMaintenancePlan” package.
3. Select Package Location as File System and browse for Package path to save “DBMaintenancePlan.dtsx” file.
4. Save and OK to complete the export process in SERVER-1.
5. Transfer the newly exported file to new server file system (say SERVER-2).
6. Transfer any required Rebuild Indexes and UpdateStatistics SPs from SERVER-1 to SERVER-2(if any).
7. Connect to Integration Services of SERVER-2. (use step 1). Import the previously exported package.
8. Connect to Database Engine of SERVER-2
9. Verify imported Maintenance Plan on SERVER-2.
10. Modify Plan to change some config entries.
11. Follow below steps ONLY IF WANT TO change Job Schedule Timing.
Calendar button present right side of “Schedule” tab. Change appropriately and
save the changes.
Below Step 12 to 14 are for “Nightly_DB_maintenance” Subplan.
Click on Nightly_DB_maintenance Subplan to see below screen.
12. Verify Rebuild and update statistics tasks.
13. Verify Database Backup Location and database names.
=> Double click on “Full Backup Database task”.
14. Verify Backup File Cleanup Period.
=> Double click
on “CleanUp BackUp Files”.
=> Verify File
Age. (Prefer to have 2 weeks)
Below Step 15 is for “Every2Hrs_DB_Tran_Bkp” Subplan.
Click on Nightly_DB_maintenance Subplan to see below screen
15. Verify Transactional backup location and databases names.
on “Back Up Database Task”
16. Save the Maintenance plan and close it.
How to VERIFY Maintenance Plan Jobs?
VERIFY Maintenance Plan Jobs:
1. Open property window of both the jobs (right click on Job name)
2. Job owner:
=> If face any access related issue with Vadmin
then change Job owner to “SA”.
=> We are
facing this issue in VS12345 because of SQL agent service default
3. Review Schedule Time etc.
धन्यवाद मित्रो !!