BE 15 - how to backup SQL database on one server and restore to another server

[CWTokyo]

Hello,

I'm running BE 15 on Windows Server 2008. I have 2 SQL servers; a production and a test. I made a backup of the production server's SQL databases and would like to restore them to the test server. But when I choose "Restore", the only server that appears is the production server so I can't restore to the test server. Similarly, if I go to the test server and choose "restore", it only shows itself as the point to restore from.

Is there a way to move from server to server in database backup and restore using BE? We're testing this out assuming that our production server totally crashes and we have to bring a spare online someday.

Thank you.

[pkh · Accepted solution]

To restore a database to another SQL instance, be it on the same server or another server, you need to use SQL Redirection.  Edit your job to do so.  You would be allowed to specify which instance you want to restore to.

Make sure that the target SQL instance is of the same SQL version as a the source SQL instance.  This is a MS restriction.

[VJware]

Is the Remote Agent installed on the target SQL server ?

And are you trying to restore the System DBs (such as Master, Model) or User DBs ?

Have a look @ the Agent for SQL chapter of the Admin Guide as well.

[CWTokyo]

@pkh - Thank you for the reply. Where is that option? I've opened the job for editing and I see there are options for Microsoft SQL, but redirection isn't there. 

I can choose Backup Method (Full, Full copy, Database Snapshot, Log No Truncate and Log), Plus things like consistency check. I don't see SQL redirection listed there. 

[CWTokyo]

@VJware - 

1. Yes, agent is installed

2. Just a user DB

3. I can but I would like to hear more about SQL redirection.

[pkh]

You are looking at the backup job. Take a look at the SQL restore job

[VJware]

When you go through the prompts of the Restore Wizard and after selecting the data which is to be restored, you get an option to Restore to original location or Restore to a different location. Choose the latter one.
 

[CWTokyo]

That did it! I was able to choose the test server as the destination. Thank you!