Solved

SQL Restore Script - Syntax Error

Posted on 2016-11-07
8
105 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
  • 4
  • 3
8 Comments
 
LVL 49

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 13

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 49

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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 49

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 49

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

679 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