Solved

SQL Restore Script - Syntax Error

Posted on 2016-11-07
8
130 Views
Last Modified: 2016-11-08
Full disclosure - I am very nearly a total SQL newbie.

I am trying to follow work instructions put together by our previous DBA to build a SQL 2012 server. I have the server in place and have followed the process to manually create the relevant DBs and I am now trying to restore to them.

Below is a copy of the script that I am running to do this.

RESTORE DATABASE GenComms
FROM DISK = ‘C:\SPR_Backups\GenComms_backup_2016_11_07_010009_6357511.bak’
WITH MOVE 'GenComms' TO 'X:\SPR DATA\GenComms.mdf',
MOVE 'GenComms_Log' TO 'T:\SPR TRANS\GenComms.ldf',
NoRecovery, Replace, Stats=10


When I run this I get the following errors.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '‘'.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Can anyone help ? Please let me know if you need any further information.

TIA
0
Comment
Question by:rob preston
[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
  • 4
  • 3
8 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41877138
Are you sure you paste the command correctly?
The error is about a single quote and the code above looks OK in terms of single quotes.
Can you post a screen shot with the command and respective error highlighted?
0
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 250 total points
ID: 41877170
The moment I copied it over to SSMS, I noticed the qoute near ...FROM DISK = 'C:\.... was not as expected. I have just removed and re-added the expected single-quote. Can you try now?

RESTORE DATABASE GenComms
FROM DISK = 'C:\SPR_Backups\GenComms_backup_2016_11_07_010009_6357511.bak'
WITH MOVE 'GenComms' TO 'X:\SPR DATA\GenComms.mdf',
MOVE 'GenComms_Log' TO 'T:\SPR TRANS\GenComms.ldf',
NoRecovery, Replace, Stats=10

Open in new window

0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41877185
Good catch, Nakul.
I just did the same when I read your comment and the quote isn't the same as the rest of the single quotes.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:rob preston
ID: 41877350
Thank you for the quick update.

My error message has now moved on. I understand the issue, I'm just not sure as a newbie how I work around it....

Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'GenComms' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41877371
You can't restore a database while users are connected to it. This means you also should disconnect from the database.
0
 

Author Comment

by:rob preston
ID: 41877380
Nobody is connected to it at the moment other than myself having SSMS open. If I close that how do I then run the commands to do the restore ?

Please excuse the question - I'm sure it's an obvious answer but I am totally new to this.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41877382
Nobody is connected to it at the moment other than myself having SSMS open.
This means is you that is connected. In SSMS try to move the cursor to another database (for example master) and the run the script again.

If that not works you can always use the following piece of code before your restore command
ALTER DATABASE GenComms SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
GO
USE master
GO
RESTORE DATABASE GenComms
FROM DISK = 'C:\SPR_Backups\GenComms_backup_2016_11_07_010009_6357511.bak'
WITH MOVE 'GenComms' TO 'X:\SPR DATA\GenComms.mdf',
MOVE 'GenComms_Log' TO 'T:\SPR TRANS\GenComms.ldf',
NoRecovery, Replace, Stats=10

Open in new window

0
 

Author Comment

by:rob preston
ID: 41878980
My restores are now completed. Many thanks for all the help !
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

628 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