Solved

WITH MOVE - OR - WITH REPLACE help??

Posted on 2007-11-19
12
839 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
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.

 

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
 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Replication question 9 42
Querying data from 3 SQL tables 2 32
T-SQL: "HAVING CASE" Clause 1 25
Syntax for query to update table 2 12
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

773 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