?
Solved

WITH MOVE - OR - WITH REPLACE help??

Posted on 2007-11-19
12
Medium Priority
?
865 Views
Last Modified: 2008-02-01
Hello,
I am a beginnger at sql 2005.  I have backed up my database from our production server and copied it down to my local network.  I am trying to restore the *.bak file on a local server running sql 2005.
I am using the following code:
USE master
RESTORE DATABASE St****on
FROM DISK = 'C:\BAckup\St*****on\St****on_backup_200711160030.bak'
WITH REPLACE
, MOVE 'St*****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
, MOVE 'St*****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'

When I run this code I get the following error:
Msg 3176, Level 16, State 1, Line 2
File 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' is claimed by 'St****on_Log'(2) and ''St****on_Data'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

When I change my code to use the WITH MOVE Command :
USE master
RESTORE DATABASE 'St****on
FROM DISK = 'C:\BAckup\'St****on\'St****on_backup_200711160030.bak'
WITH REPLACE
, WITH MOVE ''St****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
, MOVE ''St****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'

I then get the following error:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Any help would be greatly appreciated:
Thanks
John

0
Comment
Question by:hexvader
[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
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 164 total points
ID: 20314314
Don't you just have a couple extra single quotes (right after the word move)?
0
 
LVL 21

Expert Comment

by:mastoo
ID: 20314339
Maybe the following (I added the target filenames also):

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\BAckup\MyFile.bak'
WITH MOVE 'St****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyData.mdf',
MOVE 'St****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyLog.ldf'
0
 
LVL 6

Assisted Solution

by:wshark83
wshark83 earned 164 total points
ID: 20314430
go
restore database St****
from disk = 'C:\BAckup\St*****on\St****on_backup_200711160030.bak'
with
  move 'St*****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' ,
  move 'St*****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'      , Replace
GO
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:hexvader
ID: 20314466
Ok - i just tried this code:

USE master
RESTORE DATABASE 'St****on
FROM DISK = 'C:\BAckup\'St****on\'St****on_backup_200711160030.bak'
WITH REPLACE
WITH MOVE ''St****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'St****on_Data.mdf'
MOVE ''St****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'St****on_Log.ldf'

I received the following error:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

John
0
 

Author Comment

by:hexvader
ID: 20314497
wshark83,
I just tried this code:
USE master
RESTORE DATABASE St*****on
FROM DISK = 'C:\BAckup\St*****on\St*****on_backup_200711160030.bak'
WITH
MOVE 'St*****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\St*****on_Data.mdf'
MOVE 'St*****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\St*****on_Log.ldf'
REPLACE
GO

I got the following error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'MOVE'.

john
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 172 total points
ID: 20314662
you need to separate the "WITH " parts with commas:
USE master
RESTORE DATABASE St*****on
FROM DISK = 'C:\BAckup\St*****on\St*****on_backup_200711160030.bak'
WITH  MOVE 'St*****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\St*****on_Data.mdf'
, MOVE 'St*****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\St*****on_Log.ldf'
, REPLACE
GO

Open in new window

0
 
LVL 6

Expert Comment

by:wshark83
ID: 20314751
hexvader the move statement has to be on one line for the data as per code. and don't forget , before replace. both my code and angelIII's code are exactly the same

just check the data file and log file are named currectly run this statement first

Go
restore filelistonly
from disk = 'C:\BAckup\St*****on\St*****on_backup_200711160030.bak'
GO

0
 

Author Comment

by:hexvader
ID: 20315330
Ok - I ran this code:
Go
restore filelistonly
from disk = 'C:\BAckup\St*****on\St*****on_backup_200711160030.bak'
GO
And it ran successfully - NO problems

I then Ran this code:
USE master
RESTORE DATABASE st*****on
FROM DISK = 'C:\BAckup\st*****on\st*****on_backup_200711160030.bak'
WITH  MOVE 'st*****on_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Data.mdf'
, MOVE 'st*****on_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Log.ldf'
, REPLACE
GO

And received the following error messages:
Msg 9953, Level 16, State 1, Line 3
The path 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ft_st*****on' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive.
Msg 3156, Level 16, State 50, Line 3
File 'sysft_ft_st*****on' cannot be restored to 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ft_st*****on'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3

John
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
0
 

Author Comment

by:hexvader
ID: 20319381
When I run this code:
Go
restore filelistonly
from disk = 'C:\BAckup\St*****on\St*****on_backup_200711160030.bak'
GO
I runs successfully and gives me the path and logical and physical names of the files.
I tried to run this code based on the path and names from above:
USE master
RESTORE DATABASE st*****on
FROM DISK = 'C:\BAckup\st*****on\st*****on_backup_200711190030.bak'
WITH  REPLACE
, MOVE 's:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Data'
, MOVE 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Log'
, MOVE 's:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sysft_ft_st*****on' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sysft_ft_st*****on'
GO
But received the following error message:
Msg 3234, Level 16, State 2, Line 3
Logical file 's:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Data' is not part of database 'st*****on'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

The data is coming from a production server thus the long S:\path - But I thought the REPLACE option is supposed to correct the path??

Sorry guys - I know this should not be this difficult - I am kind of getting frustrated myself.
Any other possible suggestions to what I am doing wrong?
Thanks
John
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20319920
>MOVE 's:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Data'

the logical name does not have any path in it... it's just a logical name:

MOVE 'st*****on_Data'

0
 

Author Comment

by:hexvader
ID: 20320083
Ok - I think we are getting someplace:
I ran the following code:
USE master
RESTORE DATABASE st*****on
FROM DISK = 'C:\BAckup\st*****on\st*****on_backup_200711190030.bak'
WITH  REPLACE
, MOVE 'st*****on_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Data'
, MOVE 'st*****on_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\st*****on_Log'
, MOVE 'sysft_ft_st*****on' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sysft_ft_st*****on'
GO

And it was running then came back with this error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Do I have to stop the current database before I do the restore?
John
0
 

Author Comment

by:hexvader
ID: 20320438
Ok - now it all makes sense.
The job finally ran and it was because I was given the wrong database name so:
it should have been
RESTORE DATABASE ST*****on_Reporting
Ran like a charm when I changed this !!
Thanks guys for your help and patience.
john

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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