Solved

Restore Database With MOVE

Posted on 2010-11-15
6
759 Views
Last Modified: 2012-05-10
Hi guys!  

I'm stumped with this one and I am hoping you could help me.

I am trying to restore a database into another server.  I ran a RESTORE FILELISTONLY and this is what I got.

RESTORE FILELISTONLY FROM DISK = '\\SomeSharedDrive\Backup$\SomeFolder\DummyDB_LIVE.bak'

DB_data      D:\SQLServer\MSSQL\Data\DummyDB_LIVE_data.MDF      D      PRIMARY      292749312      35184372080640
DB_log      E:\SQLServer\MSSQL\Data\DummyDB_LIVE_Log.LDF      L      NULL      104595456      35184372080640


I ran the following command:


Restore database DummyDB_LIVE
from disk = '\\SomeSharedDrive\Backup$\SomeFolder\DummyDB_LIVE.bak'
with move 'DB_data' to 'F:\SQLServer\MSSQL\data\DummyDB_LIVE_data.mdf',
move 'DB_log' to 'G:\SQLServer\MSSQL\data\DummyDB_LIVE_Log.ldf',
replace

I got this error:
Msg 3156, Level 16, State 1, Line 3
File 'DB_log' cannot be restored to 'G:\SQLServer\MSSQL\data\DummyDB_LIVE_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.


I can confirm that the paths are correct.  The user account used by SQL Server service is permissioned with read/write access to its respective folders.  

SQL server version is SQL Server 2000.

What else am I missing?
0
Comment
Question by:monicai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34142315
Does it work if you give it a different location?
-G
0
 
LVL 4

Author Comment

by:monicai
ID: 34142348
Btw, this restore is basically overwriting an existing DummyDB_LIVE database (on a different server) which has exactly the same mdf and ldf paths specified in the restore statement.

I did try doing the restore on a new database with the same directory path but slightly different filenames and it works.  

For some reason the RESTORE fails to overwrite the LDF file.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 34142526
This should work:

Restore database DummyDB_LIVE
from disk = '\\SomeSharedDrive\Backup$\SomeFolder\DummyDB_LIVE.bak'
with move 'DB_data' to 'D:\SQLServer\MSSQL\Data\DummyDB_LIVE_data.MDF',
move 'DB_log' to ' E:\SQLServer\MSSQL\Data\DummyDB_LIVE_Log.LDF',
replace

If you intend to move your MDF and LDF files to F and G drives, then you have to detach your database and copy your MDF and LDF files to F and G drive and attach it to get it done instead of using RESTORE WITH MOVE option.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34147498
From what you are saying, a different SQL instance is using those files in the location you are trying to restore to.

This will not work.  SQL Server will not let you have the files.  They are in use.  You will have to restore to a different path or a different file name.  

What exactly are you trying to do?  Are you trying to restore over the database on the other instance?

-G
0
 
LVL 4

Author Comment

by:monicai
ID: 34170184
I have resolved this issue by detaching and attaching the database then re-run the same restore query.
0
 
LVL 4

Author Comment

by:monicai
ID: 34170189
Btw thanks to all who has answered this question.  Points has been awarded.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question