none
Recover all sql databases to new sql server

    Question

  • I am setting up a new hardware for our servers. 

    The SQL server is today running between 800 and 900 databases. 

    To move all theese databases i have found the easiest way is to use DPM to restore them and leave them operational on the new server. And use sql scripts to copy logins and so on. I have done this in a lab setup for this environment allready and it works great. Sql copy database does not work at all for me whith this setup.

    So the only problem i have left is i do not want to recover the databases one by one using the gui :/ 

    I have seen different scrips on different forums but not one that does just this. I am not skilled at all in powershell, so editing them to my use without some guidance is not very easy.

     

    What i would like to to is recover ALL databases (except  the system databases?) from latest recoverypoint to a new sql server, and leave all databases operational on the new server.

    Both servers will be in same domain.

     

    Could some one give me some direction, or maby someone have a script like this allready?

    Monday, October 31, 2011 1:44 PM

Answers

  • Yes,

    If the OS version where the backup was taken is different from where the backups are being restored, then the restore will fail if you try to restore to a SQL instance. In this scenario the only way is to restore to a network folder and then use SQL Server Management Studio to attach the restored DB.

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Fredrik Weme Tuesday, November 01, 2011 11:11 AM
    Tuesday, November 01, 2011 7:32 AM

All replies

  • Hi Fredrik,

     

    I can try to work on something out for you...

    The OS version on the server you have taken the SQL backup from is the same OS version running on your new SQL server?


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, October 31, 2011 8:51 PM
  • Hi and thank you for answering :)

    The old server is running SQL Server 2005 on a 32bit server

    The new server will run SQL server 2008 R2 on a 64bit server

     

    Good thing you pointed this out. I was using all my energy on solving the problems with logins and their links to the databases. When i solved this i i felt that all other problems would be miniscule :) 

     

    Will it be a big problem that they are on different servers. Should i upgrade the old sql server to 2008 r2 first, although if that is avoidable it would be good. Since there are 800 possible failures here :) lol

    Would probably be safer to install sql server 2005 on the new server and upgrade it after the recovery, since then i would allways have the old untouched server to fall back too.

     

    Any suggestions are appreciated :)

    Tuesday, November 01, 2011 7:25 AM
  • Yes,

    If the OS version where the backup was taken is different from where the backups are being restored, then the restore will fail if you try to restore to a SQL instance. In this scenario the only way is to restore to a network folder and then use SQL Server Management Studio to attach the restored DB.

     


    Thanks, Wilson Souza - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Fredrik Weme Tuesday, November 01, 2011 11:11 AM
    Tuesday, November 01, 2011 7:32 AM
  • Ok thank you, will have to do some more testing then, and se if i can make all the logins work when detaching and attaching the databases

     

    Really appreciate the quick answers :)

     

    Tuesday, November 01, 2011 9:04 AM