Solved

WITH MOVE - OR - WITH REPLACE help??

Posted on 2007-11-19
12
834 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
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 41 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 41 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
 

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 43 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 142

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now