Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Restore Script - Syntax Error

Posted on 2016-11-07
8
Medium Priority
?
136 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 52

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

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 52

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 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

715 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