July 10, 2017 at 4:21 am
Hello,
I have imported SQL Server Maintenance Plan (which takes Full & T-log backup of user databases) from one server to other. On the new server, most of the Maintenance Plan options are editable(like backup location, log directory) except DB name.
Database names are still listing from old server. Is there anyway he can update the Maintenance Plan to list databases on new server, without recreating the plan from scratch? Followed below to import Maintenance Plan
http://www.sqlballs.com/2011/03/automate-deploying-maintenance-plans-in.html
Thanks in adavance
July 10, 2017 at 5:43 am
Sreejith! - Monday, July 10, 2017 4:21 AMHello,
I have imported SQL Server Maintenance Plan (which takes Full & T-log backup of user databases) from one server to other. On the new server, most of the Maintenance Plan options are editable(like backup location, log directory) except DB name.
Database names are still listing from old server. Is there anyway he can update the Maintenance Plan to list databases on new server, without recreating the plan from scratch? Followed below to import Maintenance Planhttp://www.sqlballs.com/2011/03/automate-deploying-maintenance-plans-in.html
Thanks in adavance
Open the package in BIDS and edit the Connection Manager. That might be the issue. Can you post some screenshot of your package and how u trying to edit?
July 10, 2017 at 6:42 am
VastSQL - Monday, July 10, 2017 5:43 AMSreejith! - Monday, July 10, 2017 4:21 AMHello,
I have imported SQL Server Maintenance Plan (which takes Full & T-log backup of user databases) from one server to other. On the new server, most of the Maintenance Plan options are editable(like backup location, log directory) except DB name.
Database names are still listing from old server. Is there anyway he can update the Maintenance Plan to list databases on new server, without recreating the plan from scratch? Followed below to import Maintenance Planhttp://www.sqlballs.com/2011/03/automate-deploying-maintenance-plans-in.html
Thanks in adavance
Open the package in BIDS and edit the Connection Manager. That might be the issue. Can you post some screenshot of your package and how u trying to edit?
In the attached file,we can see that DB name starts with "V", which is not listing in Maintenance Plan DB list(still showing DB list from old server where I exported the plan)
July 10, 2017 at 7:46 am
As VastSQL said, your connection manager for the package is likely pointing to the old server
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
July 10, 2017 at 7:55 am
You have a couple options, none of which are super simple.
OPTION 1 - Edit in BIDS or SSDT
As already mentioned, you can open the maintenance plan with with SQL Server Development Toold (SSDT) or BIDS (depending on the version of SQL and Visual Studio you use). You would change the connection object to your new server.
OPTION 2 - Recreate the package
This should not be as painful as it sounds because you can copy/paste some of the tasks from the old package to the new one.
On a side note: there's better alternatives SQL Server maintenance plans. Keeping in mind tgat they are. SSIS packages, I think its preferable to create real SSIS packages to handle maintenance tasks or https://ola.hallengren.com
-- Itzik Ben-Gan 2001
July 10, 2017 at 8:17 am
From SSMS , I am able to add a new connection(under Manage Connection),but unable to Remove existing connection to old server. Hope this limitation will be there in BIDS also. Will try with BIDS & update you
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply