Solved

WITH MOVE - OR - WITH REPLACE help??

Posted on 2007-11-19
12
841 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

829 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