locked
Migration - Copy Database Wizard RRS feed

  • Question

  • Hi,

    I am going to migrate our SQL Server 2008R2 to a new environment (SQL Server still will be 2008 R2).

    I was planning to use the 'Copy Database Wizard' as I have 20+ databases to migrate. I thought this would be the best solution as it also handles user login migration.

    However, in this msdn article on migrating SSRS, it states... "Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database."

    Is this correct? I thought it was Backup and Restore that does not migrate logins?

    Thanks!

    Thursday, June 19, 2014 9:09 AM

Answers

  • Hi,

    Best way to migrate database is by using backup and restore method.

    You can use below link to transfer logins

    http://support.microsoft.com/kb/918992


    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 Articles

    Thursday, June 19, 2014 9:25 AM
  • Is there any particular reason for this? The Copy Database Wizard handles login migration so would this not be better?

    There are some limitations it can be or cannot be applicable to your case. Please read below link for detailed information, read limitations, recommendations and prerequisites section)

    http://msdn.microsoft.com/en-GB/library/ms188664.aspx#Restrictions

    From MSDN link you posted

    The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

    Above is correct totally


    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 Articles

    Thursday, June 19, 2014 10:11 AM
  • it does not preserve permission settings in the database.

    I don't understand this piece. So the Copy Wizard does copy SQL Server logins, but does not set Database users? This correct?

    To understand please read below, especially the section permission

    http://technet.microsoft.com/en-us/library/ms187580.aspx#credentials


    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 Articles

    Thursday, June 19, 2014 11:33 AM

All replies

  • Hi,

    Best way to migrate database is by using backup and restore method.

    You can use below link to transfer logins

    http://support.microsoft.com/kb/918992


    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 Articles

    Thursday, June 19, 2014 9:25 AM
  • Hi,

    Before migrating it take the proper backup of your database.

    You can migrate it but dont copy it before backup. do remember to close rest of the application during the time of migration.


    Regards, Ravi Kumar

    Thursday, June 19, 2014 9:27 AM
  • Hi,

    Best way to migrate database is by using backup and restore method.

    You can use below link to transfer logins

    http://support.microsoft.com/kb/918992

    Is there any particular reason for this? The Copy Database Wizard handles login migration so would this not be better?
    Thursday, June 19, 2014 9:47 AM
  • Is there any particular reason for this? The Copy Database Wizard handles login migration so would this not be better?

    There are some limitations it can be or cannot be applicable to your case. Please read below link for detailed information, read limitations, recommendations and prerequisites section)

    http://msdn.microsoft.com/en-GB/library/ms188664.aspx#Restrictions

    From MSDN link you posted

    The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

    Above is correct totally


    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 Articles

    Thursday, June 19, 2014 10:11 AM
  • it does not preserve permission settings in the database.

    I don't understand this piece. So the Copy Wizard does copy SQL Server logins, but does not set Database users? This correct?
    Thursday, June 19, 2014 11:17 AM
  • it does not preserve permission settings in the database.

    I don't understand this piece. So the Copy Wizard does copy SQL Server logins, but does not set Database users? This correct?

    To understand please read below, especially the section permission

    http://technet.microsoft.com/en-us/library/ms187580.aspx#credentials


    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 Articles

    Thursday, June 19, 2014 11:33 AM