Restore to another server

darmstrongdarmstrong Posts: 8
edited December 23, 2010 12:46PM in SQL Backup Previous Versions
I am in the middle of a large project to migrate databases from SQL 2005 to SQL 2008. SQL Backup 6 has been used to backup the databases from the SQL 2005 server for the last year. I have installed a trial license of the software on the SQL 2008 server.

How can I redirect SQL 2005 backup sets so that they will restore on the SQL 2008 server?
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    What do you mean by redirect?

    You can just restore the backup files created by SQL Backup on the SQL 2005 instance to the SQL 2008 instance, by selecting the files to restore in the GUI, or running the restore manually using the extended stored procedures e.g.
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [h:\backups\AdventureWorks.sqb] WITH REPLACE" '

    You might need to move files around, in which case use the MOVE option as per regular SQL Server restores e.g.
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [h:\backups\AdventureWorks.sqb] WITH REPLACE, MOVE [AdventureWorks] TO [e:\sqldata\AdventureWorks.mdf], MOVE [AdventureWorks_log] TO [e:\sqldata\AdventureWorks.ldf]" '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • darmstrongdarmstrong Posts: 8
    Petey,

    The GUI does not let me select the other server. The only option it gives me is to restore it to the same server it was created from. Is there something special that I need to do to use the GUI to restore to the other server?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Thanks for the clarification.

    The GUI will only allow you to restore to the currently selected server. Thus, if server A is your SQL 2005 instance, and server B your SQL 2008 instance, you need to register server B in the GUI. Then select server B, and start the restore process from there.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • darmstrongdarmstrong Posts: 8
    I tried that. When I select Server B it does not allow me to select any of the backup sets that were created on Server A.

    There is an option to Add Server in the file browser, but it gives the caveat that only servers visible to Server B are available for browsing. What does it mean for a server to be "visible"? I can ping and browse network shares on Server A from Server B.
    · Share on Twitter
  • darmstrongdarmstrong Posts: 8
    As another thought, it is possible to copy the .sqb files from Server A to Server B and then restore them from Server B?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    When you're connected to server B, you can only access directories on that server. I'm assuming your backup files are still on server A, which is why you can't select them. I guess you have also shared the directory on server A where the backup files are located.

    In order to be able to access the network share from server A, your SQL Server logon user on server B needs to have rights to access the network share. This is the user account you use in the SQL Backup GUI to connect to server B. However, sometimes these sort of remote browsing doesn't work.

    Another additional requirement is that the SQL Backup Agent service startup account on server B needs to also have rights to read from the network share.

    Basically, if remote browsing doesn't work, try restoring manually instead of using the GUI. You then only need to ensure that the SQL Backup Agent service startup account has the necessary rights.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    darmstrong wrote:
    As another thought, it is possible to copy the .sqb files from Server A to Server B and then restore them from Server B?
    Yes.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.