Backup Exec 2010 - How to restore a SQL database onto a different server?

[Routledge1982]

The situation is as follows

Server 1

Backup of lbusiness critical SQL database, taken and stored on LTO2 tape via Backup Exec 2010 with SQL Server Agent

The .MDF file for this was stored on the servers D drive

The .LDF file for this was stored on the servers F drive

I want to put this database onto server 2 for disaster recovery / testing purposes. Server 2 currently has an out of date version of the business critical database on it.

Server 2

Have put the out of date databse in single user mode

Have put in the LTO2 tape from server 1, into server 2 and ran a restore using SQL Server Agent

The out of date database MDF file for this is stored on the servers D drive

The out of date database LDF file for this is stored on the servers D drive

In the restore options i have chosen

File Direction - Redirect files to D\NEW FOLDER

General - Restore over exiting files

MicrosftSQL - overwrite the exisiting database

my job fails saying this (even though my database is in single user mode)

Restore-

 V-79-65323-3101 -
An error occurred on a query to database Will Replication Test.
V-79-65323-3101 - Exclusive access could not be obtained because the database is in use.
 

any idea what im doing wrong?

does it matter the location of .mdf and .ldf are different for each server?

grateful for any help!

[AmolB]

Refer to http://www.symantec.com/docs/TECH74302

[Routledge1982]

thank you for that link mate

Can i please just confirm with you that the steps i should follow for a succesfull restore of a database onto a different server are

1. Put the old out of date database in single user mode
2. Launch backup exec and select restore job
3. Select the database that is on the LTO tape drive
4. Under Settings- Microsoft SQL, select the options "Take existing database offline" and "Overwrite existing database"  and run the redirected restore.
5. Do i need to specify where i want the .df and .ldf files to be stores?

your help is so greatly appreciated

[AmolB]

You don't need to specify the path for mdf and ldf files

Refer to the below article the process is expalined in simple steps.

http://www.symantec.com/docs/TECH56694

Also refer to the below blog

https://www-secure.symantec.com/connect/articles/restore-i-redirect-restore-sql-database-another-mac...

[Routledge1982]

Thank you very much, 1 final question if you dont mind, regarding the below

https://www-secure.symantec.com/connect/articles/restore-i-redirect-restore-sql-database-another-mac...

To perform a Redirected Restore of a database:

1. Ensure SQL 2000 or SQL 2005 and relevant service packs are installed on the target server similar to the source server when the database was backed up

NOTE: A SQL 2005 database can not be redirected to a SQL 2000 server

2. Open Backup Exec
3. On the navigation bar, click Restore
4. On the Properties pane, under Source, click Selections
5. Select the database to restore. (Figure 5)

6. On the Properties pane, under Destination, click Microsoft SQL Redirection.
7. Check the box for Redirect Microsoft SQL Server sets.
8. Enter the destination server name or alternate database name if redirecting to the original server but a different database.

NOTE:  If the database the restore is being redirected to does not exist, the restore will create the new database during the restore process.

9. Select the instance and database name (if any) to redirect the database to.
10. Select the destination options (if any) to change the destination of the database files. (Figure 6)

Do points 6 , 7 , 8 , 9 , 10 relate to my situation?

I am doing the restore physically on server 2 and not remotely, and there is already an instance of the database on server 2 (althought it is out of date)

thank you very much

Is this still classed as SQL redirection and i need to follow those steps to put in my servers name etc?

[AmolB]

You need to follow the steps as you are not restoring the DB on the original server.

SQL redirect comes in picture as you are restoring DB on a different server.

[Routledge1982]

thank you so much, i will try this on Monday and let you know how i get on

[Routledge1982]

hi there, i am attempting to restore just now

It is being restored on a DIFFERENT server, however there is an OUT OF DATE version of the database on this server

8. Enter the destination server name or alternate database name if redirecting to the original server but a different database. (MY SERVER IS CALLED SERVER 2 - DO I JUST ENTER SERVER 2 HERE?)

9. Select the instance and database name (if any) to redirect the database to. (CAN I LEAVE THIS BLANK AS THERE IS AN OUT OF DATE VERSION OF THE DATABASE ON THIS SERVER or DO I ENETER THE NAME OF THE OUT OF DATA DATABASE?? BOTH DATABASES ON SERVER 1 AND 2 HAVE THE SAME NAME)

10. Select the destination options (if any) to change the destination of the database files. (Figure 6) (CAN I POINT TO A NEW FOLDER HERE TO MOVE THE MDF AND LDF FILES?

do i also  need to tick the options that say

"take existing database offline"

and

"restore over existing database"

many thanks

[pkh]

8. Yes

9. Specify the instance name, but not the database name

10. Yes.

11. Yes to both.

[Routledge1982]

thank you so much

9. Specify the instance name, but not the database name

what exactly is the instance name?

[pkh]

In simple terms, a SQL instance is a logical grouping of SQL databases.