asked on

How to restore SQL Server dump file

Hi Guru:

I work with Oracle but is completedly new to SQL Server. I was required to create database and restore from a dump file (I would think that it likes oracle's import). The dump file was created by SQL Server's backup command(I would think it likes oracle's export). But I got errors like:

Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\dev_Data.MDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 3
File 'vwdev_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\dev_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 3

I use the command as:

restore database dev
from disk = 'C:\TEMP\James\dev.bak'
go

Any suggestions, thanks.

ASKER CERTIFIED SOLUTION

ChrisFretwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arbert

Agree with chris--if you're restoring to a different directory structure, you're going to have to use MOVE...TO. Also, if the directory structure is the same and you're restoring over an existing database, you need to use the WITH REPLACE option.

Brett

EugeneZ

just one thing:
logical name is not alwayse like databse name - looks like (from error above) you have not 'dev' but 'vwdev_Data'
for dat file:

restore database dev
from disk = 'c:\temp\james\dev.bak'
with move 'vwdev_Data' ' to 'c:\dev_data.mdf',
move 'dev_log' to 'c:\dev_log.ldf' ---

--to see logical name of the dtabase - run in Query Analyzer -
use yourdatabsenamesource
go
select name,filename from sysfiles