Solved

SQL Restore Script - Syntax Error

Posted on 2016-11-07
8
70 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 45

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 11

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 45

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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

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 45

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore 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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

13 Experts available now in Live!

Get 1:1 Help Now