locked
Unable to copy a DB from a instance A to isntance B within SQL server 2008 R2 RRS feed

  • Question

  • I am trying to copy the database from instance A to instance B within same SQL server (2008 R2)

    from PROD to TEST SQL

    and receiving the following error message. please help.

     at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
    InnerException-->Login failed for user 'domain\TESTSQL$'.

    Tuesday, July 28, 2015 2:39 PM

Answers

  • You may want to read up on the Copy Database Wizard.  It works great with small databases but may not work at all with larger databases.

    Microsoft posted an article on some methods to migrate databases where the Copy Database Wizard is also mentioned.  Strangely enough, I don't think it mentions the detach - attach method.

    https://msdn.microsoft.com/en-us/library/ms189624.aspx

    Tuesday, July 28, 2015 6:29 PM

All replies

  • Also have the below error message in the log

    The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure.
    This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Tuesday, July 28, 2015 2:41 PM
  • You need SysAdmin permissions on the instance you are attempting to copy the database to as you are attempting to create a database.

    Either obtain the sa password for the second instance so that you can use the sa account; or have the SQL Server DBA for the second instance add you as a System Administrator.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, July 28, 2015 2:43 PM
  • hi Tony,

    I tried both ways, and still receiving the same error message.

    Tuesday, July 28, 2015 2:55 PM
  • Does the domain\TESTSQL$ login have access to both servers?  The error message is stating that the login failed for one of the instances.  I would guess that it does not have access to connect to the database engine for your production environment.
    Tuesday, July 28, 2015 3:03 PM
  • Ok; have you checked you can connect to the second instance?  Connect to it and try and create a new database.

    Are the two editions of SQL Server the same version; e.g both are Standard or both are enterprise?  If Instance A is Enterprise and Instance B is Standard then this could be an issue if you are using features that are not supported by Standard (for example Table Partitioning).

    Is Instance B SQL Server express and the database is bigger than 10Gb?  SQL Server Express can only support a Database up to 10Gb in size.


    Please click "Mark As Answer" if my post helped. Tony C.


    Tuesday, July 28, 2015 3:25 PM
  • I would suggest not to use Transfer database task you are using that to move database it has limitations and I have seen many a times people reporting issue. I am not sure its directly related to login issue

    I would suggest to backup the database and restore it on destination


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, July 28, 2015 4:20 PM
  • Shanky,

    I have used Copy database wizard not the transfer database task from SSIS ? the problem with restore and backup is that you have to manually do all of those and run the script to transfer the user logins again.

    can you help me if there is a way I could copy all the database from Instance A to instance B within same SQL server (2008 r2) at once , instead of doing one at a time...

    any suggestion will be appreciated.

    Tuesday, July 28, 2015 5:10 PM
  • You may want to read up on the Copy Database Wizard.  It works great with small databases but may not work at all with larger databases.

    Microsoft posted an article on some methods to migrate databases where the Copy Database Wizard is also mentioned.  Strangely enough, I don't think it mentions the detach - attach method.

    https://msdn.microsoft.com/en-us/library/ms189624.aspx

    Tuesday, July 28, 2015 6:29 PM