I am setting up new sqlserver maintenance plan, a quick way to do this is to export an existing sqlserver maintenance plan from a gold image server and import it to the new server. the steps are:
On the gold image sqlserver:
SSMS connect to SSIS, find the maintenance plan in MSDB, right click -> export, save it as a file.
On the new image sqlserver:
SSMS connect to SSIS, import the package into MSDB
SSMS connect to Database engine, find the imported maintenance plan, schedule it to run
After completion of the steps, I right click on the job and test run, it failed with error, something like below:
"Failed to acquire connection "Local server connection"."
The error is caused by the configuration of the gold image, when the maintenance plan is configured there, it uses the server name in the connection manager, and sa account for sqlserver authentication.
The fix is to open the exported file, find the line "data source" like below, and modify it to be localhost and use windows authentication, save the file, and re-import it to the new sqlserver, and it ran successful.
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=localhost;Integrated Security=SSPI;Connect Timeout=30;" />
On the gold image sqlserver:
SSMS connect to SSIS, find the maintenance plan in MSDB, right click -> export, save it as a file.
On the new image sqlserver:
SSMS connect to SSIS, import the package into MSDB
SSMS connect to Database engine, find the imported maintenance plan, schedule it to run
After completion of the steps, I right click on the job and test run, it failed with error, something like below:
"Failed to acquire connection "Local server connection"."
The error is caused by the configuration of the gold image, when the maintenance plan is configured there, it uses the server name in the connection manager, and sa account for sqlserver authentication.
The fix is to open the exported file, find the line "data source" like below, and modify it to be localhost and use windows authentication, save the file, and re-import it to the new sqlserver, and it ran successful.
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=localhost;Integrated Security=SSPI;Connect Timeout=30;" />
Comments
Post a Comment