Restore a database to another server

Hi, i am using sql server 2000, and i make daily backups. I want to
restore my backup to a test msde database i have. I don't know how i
did it last time.

-The database name is the same: "web" on both places
-The database is located on different hard drives and the file name
are different on both places.

I tried to do it from enterprise manager, restore database, from
device, i chose the transaction file and the database backup itself
and tried to restore, but i got an error that i don't have exclusive
access to the database.

How can i fix this?Seguros Catatumbo (seguroscatatumbo@.gmail.com) writes:

Quote:

Originally Posted by

Hi, i am using sql server 2000, and i make daily backups. I want to
restore my backup to a test msde database i have. I don't know how i
did it last time.
>
-The database name is the same: "web" on both places
-The database is located on different hard drives and the file name
are different on both places.
>
I tried to do it from enterprise manager, restore database, from
device, i chose the transaction file and the database backup itself
and tried to restore, but i got an error that i don't have exclusive
access to the database.

RESTORE DATABASE db FROM DISK ='filenamehere'
WITH MOVE 'logicalname1' TO 'path.mdf',
MOVE 'logicalname2' TO 'path_log.ldf',
REPLACE

logicalname1/2 are the logical names of the device file. You find these
with help of sp_helpdb on the source database, or RESTORE FILELISTONLY
on the backup file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

RESTORE DATABASE db FROM DISK ='filenamehere'
WITH MOVE 'logicalname1' TO 'path.mdf',
MOVE 'logicalname2' TO 'path_log.ldf',
REPLACE
>

Hey Erland, thanks for replying. I couldn't get it to work. Here's the
command:

RESTORE DATABASE db FROM DISK ='c:\publica\web_db_200711150500.bak'
WITH MOVE 'WEB_Data' TO 'c:\pruebadb\web_dos.mdf',
MOVE 'WEB_Log' TO 'c:\pruebadb\web_dos_log.ldf',
REPLACE

Here's the error message:

The file 'c:\pruebadb\web_dos.mdf' cannot be overwritten. It is being
used by database 'web'.
File 'WEB_Data' cannot be restored to 'c:\pruebadb\web_dos.mdf'. Use
WITH MOVE to identify a valid location for the file.
The file 'c:\pruebadb\web_dos_log.ldf' cannot be overwritten. It is
being used by database 'web'.
File 'WEB_Log' cannot be restored to 'c:\pruebadb\web_dos_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.

Here's the output of sp_helpdb on the source db and destination db:

source:

WEB_Data
1D:\DataWEB\Data
\web_Data.MDF
PRIMARY4348096 KBUnlimited5120 KBdata only
WEB_Log
2D:\DataWEB\Data
\web_Log.LDF
NULL32448 KBUnlimited10%log only

destination:

WEB_Data
1C:\pruebadb
\web_dos.mdf
PRIMARY2092928 KBUnlimited5120 KBdata only
WEB_Log
2C:\pruebadb
\web_dos_log.ldf
NULL1024 KBUnlimited10%log only

The c:\publica\web_db_200711150500.bak was obtained from a backup made
by sql server itself on the source server itself|||I made a mistake with the command. My database name is not db, it's
"web". So now i get the same error i get when i do with the GUI:

RESTORE DATABASE db FROM DISK ='c:\publica\web_db_200711150500.bak'
WITH MOVE 'WEB_Data' TO 'c:\pruebadb\web_dos.mdf',
MOVE 'WEB_Log' TO 'c:\pruebadb\web_dos_log.ldf',
REPLACE

Here's the error message:

Exclusive access could not be obtained because the database is in use.

Do i need to take the database offline or something?|||Ok, i think i know how to fix it.

-WEB is my default database, so when i use query analizer it opens
that database. It can't restore the database if i am connected. So
instead of figuring off how to change the default database, i
connected, changed the current db to master (use master), and then i
could take web offline with enterprise manager. Now the command seems
to be working, it will take long, the database is 4GB.

RESTORE DATABASE web FROM DISK ='c:\publica\web_db_200711150500.bak'
WITH MOVE 'WEB_Data' TO 'c:\pruebadb\web_dos.mdf',
MOVE 'WEB_Log' TO 'c:\pruebadb\web_dos_log.ldf',
REPLACE|||Yup, it worked, so the conclusion is that it would have worked from
the start if i just would have taken the database offline. Thanks for
your help