Restore Issue.

Question

Restore Issue.

darren peck

SSC Journeyman

Points: 98
More actions
February 11, 2008 at 2:49 pm

#122023

Before I ask a question please forgive me because I am new to SQL server. I am trying to restore a backup of a database to a new database and am getting an error stating to use the WITH MOVE option for a valid location. I am doing this in Enterprise Manager and don't know how to proceed. I am not real familiar with the Query manager to write a restore query with the WITH MOVE command. Any help you could provide would be greatly appreciated.
Thanks,
Darren Peck

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Darren,

SQL Server reads the original file locations from the header in the backup file and it attempts to restore the database to it's original location. What SQL Server is telling you here is that you need to specify new locations for the .mdf and .ldf files. You need to change the physical file location shown on the Options screen under Restore As.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

darren peck SSC Journeyman Points: 98 More actions · Answer 2

Hi John,

I am trying to restore a backup from an existing databsae over a new database I have created. The restore path looks right to me. If I change the location, will it still restore over the new database? I have the "force restore over existing database" checked.

Thanks,

Darren

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Bit of advice, when you have an issue and are getting an error, it really helps us if you post the error message. Next it helps if you tell us which version of SQL Server you are using and how you are doing whatever you are doing. In this case, there are multiple ways to do restores.

1. you could be trying to attach files

2. you could be using Enterprise Manager and a wizard

3. you could be scripting the code

4. you could be using a third party product

5. some other method.

Knowing all of those details helps us to resolve your issue or guide you better.

So, at a minimum give us the error message and we'll see what we can do.

//added after posting//

How did you do your backup? Or do you have a backup?

-SQLBill

darren peck SSC Journeyman Points: 98 More actions · Answer 4

Ok thanks. I am trying to do a restore through Enterprise Manager with the restore wizard. I am using SQL Server 8.00.760 - SP3 (Standard Edition). Here is the error message I am receiving...

"File '005_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\015.mbf'. Use WITH MOVE to identify a valid location for the file. File '005_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\015.ldf'. Use WITH MOVE to identify a valid location for the file. RESTORE DATABASE is terminating abnormally."

Hope this is more helpful.

Thanks,

Darren Peck

I backup using the Enterprise Manager backp through a maintenance plan.

SQLBill SSC Guru Points: 51440 More actions · Answer 5

I don't use the GUI to do backup/restores. Here is the example from the BOL.

RESTORE DATABASE TestDB

FROM DISK = 'c:\Northwind.bak'

WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',

MOVE 'Northwind_log' TO 'c:\test\testdb.ldf',

REPLACE

Give that code a try after you change it to match what you need.

-SQLBill

darren peck SSC Journeyman Points: 98 More actions · Answer 6

I am going to sound pretty stupid, but as I said earlier I am new to sql. Could you be more specific using the info in the error from my previous post? I don't want to screw this up.

Thanks,

dp

darren peck SSC Journeyman Points: 98 More actions · Answer 7

OK, this is what I ran in the Query Analyzer...

RESTORE DATABASE 015

FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\005\005_db_200709270200.BAK'

WITH MOVE '005' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\015.mdf',

MOVE '005_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\015_log.ldf',

REPLACE

And this is the error I received..

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '015'.

Thanks,

dp

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Does the database 015 exist already? If not, then you aren't replacing it and need to remove the REPLACE and the comma after .ldf.

-SQLBill

John Rowan SSC Guru Points: 56440 More actions · Answer 9

Also, bracket your database name in the RESTORE DATABASE statement. SQL Server does not like your database name. Use [015] instead.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

darren peck SSC Journeyman Points: 98 More actions · Answer 10

darren peck

SSC Journeyman

Points: 98

February 12, 2008 at 4:09 pm

#777629

OK great. Thank you all very much for the help.

SQLBill SSC Guru Points: 51440 More actions · Answer 11

Good point John. I overlooked that the rule is objects should have a letter first.

-SQLBill

John Rowan SSC Guru Points: 56440 More actions · Answer 12

I cringe at using EM for backup/restore operations. I always use T-SQL. I have found that many posters have a very low comfort level with using T-SQL for administration tasks so when they ask a question about using EM, I usually answer it in the same context. In this case, if you would not have posted the T-SQL RESTORE DATABASE syntax for him, we may have never gotten as far as we did. This one was a team effort.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQLBill SSC Guru Points: 51440 More actions · Answer 13

Thanks John. I usually try to do that when I see the poster is new to SQL Server. It helps them learn something new. I agree that too many people, even experienced ones, depend a lot on the GUI. I even started using OSQL so I could learn that way of running scripts if necessary.

-SQLBill