Competition: What’s your favorite Redgate tool? Enter now.

Is that possible to restore to different server?

practicpractic Posts: 2
edited December 8, 2006 10:09AM in SQL Backup Previous Versions
I'd like to restore backup from Server A on Server B. Is that possible?
Let's say I backed up database on development server and then I'd like to restore it on QA server. Can I do that? The problem is I see only one server name in "Database to restore to SQL Server" dropdown.

Comments

  • peteypetey Posts: 2,358 New member
    If using the GUI, you can only restore a backup to SQL Server instances running on the same machine. This is because a remote server will not be able to locate the file.

    E.g. you select a backup file on Server A, in 'D:\Backups\pubs.sqb'. You cannot restore this file to Server B because that location is not valid on Server B.

    Options:

    - on Server A, open a network share containing the backup file that is accessible by the SQL Backup Agent service on Server B. In the Restore Wizard, select Server B, and enter the path to the network share and backup file manually.

    - copy the file to Server B, locate the backup file manually from the GUI, and proceed to perform the restore

    - perform the restore manually using the extended stored procedure, accesing the file via a network share e.g.

    EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs FROM DISK = [\\ServerA\shared\pubs.sqb]" '

    assuming the SQL Backup Agent service startup user has rights to access the network share.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.