Solved

WITH MOVE - OR - WITH REPLACE help??

Posted on 2007-11-19
12
836 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

910 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

21 Experts available now in Live!

Get 1:1 Help Now