2015-09-11

SQL Server - Precautions against Copy Database Wizard



The Copy Database Wizard will create an Integration Services package to copy or move database(s), there are a couple of things you need to be aware of:

1. To use the Copy Database Wizard successfully, SQL Server Agent must be started on the destination instance.

2. You must select an Integration Services Proxy account that has access to the file system on both the source and destination instances.
1) Create an Integration Services Proxy account by first creating a credential under the Security node mapping to a user that has the appropriate permissions on the destination instance.
2) Add an SSIS Package Execution Proxy mapped to the newly created credential:
I. Start SQL Server Agent (if it is disabled).
II. Expand Proxies node, right-click SSIS Package Execution and choose New Proxy.
III. In New Proxy Account dialog, specify name in Proxy name field, and map to the newly created credential in Credential name field.

3. If destination instance does not have features that have been installed on source instance, the package may not be successfully executed. For instance, if Full-Text Filter Daemon Launcher has been installed on source instance while destination instance does not have,  you will receive an error message as follows:Full-Text search is not installed, or a full-text component cannot be loaded.
Note that select Text file in Logging options within Copy Database Wizard is helpful to find more details about the encountered error.

No comments:

Post a Comment