How can you restore a SQL database to a different location or database

Got these notes from a friend who is a SQL guru. It is for SQL 7 but I think the steps are the same, just a different path.

If you are restoring a database with SQL 7.0 to an alternate location but the database name will be the same do the following:

1. create the database

2. select the database in the restore screen, right click select DB agent options.

3. Put a check mark next to Force restore over existing database

4. Select the alternate destination Server then MSSQL server

5. Perform the restore


To do a restore to an alternate database do the following:

1. select the database in the restore screen, right click select DB agent options.

2. under the section "restore database files as:" select each object and change the name of the files so that the prefix to the file name reflects the name of the new database that you want ARCserve to have created i.e.:

by default it will have the original path and file name that you were going to restore

Example:

ASDB

backed up from

d:\mssql7\data\ASDB_Log.LDF
d:\mssql7\data\ASDB_Data.MDF

but I want to restore it to a server that has the data file on C: and the new database that I want to create is call TEST, I will change it to the following:

c:\mssql7\data\TEST_Log.LDF
c:\mssql7\data\TEST_Data.MDF

and then select OK to close the window

3. For the destination I will deselect original find the server select MSSQL server and then type in a \test to make my restore path:

\\<server name>\dbasql70\test

4. run the restore

Note: In a few cases the SQL query tool had to be used in order to bring the database up after the restore using the following command:

restore database using with recovery
restore database test with recovery