January 11, 2011 at 4:12 pm
Hello, please look at syntax and tell me why I am unable to restore. I recieved a backup from another server that I am trying to restore on a dev box:
RESTORE FILELISTONLY
FROM DISK = 'C:\file_name.bak'
Returns this
Logical Name Physical Name
ICE_SampleC:\MSSQL\DATA\ICE_Sample.mdf
ICE_Sample_logC:\MSSQL\DATA\ICE_Sample_log.ldf
RESTORE DATABASE IDMine
FROM disk = 'C:\filename.bak',
MOVE 'ICE_Sample' to 'C:\ICE_Sample.mdf',
MOVE 'ICE_Sample_log' to 'C:\ICE_Sample_log.ldf',
WITH recovery
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ICE_Sample'.
Msg 319, Level 15, State 1, Line 5
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.
January 11, 2011 at 4:24 pm
hxkresl (1/11/2011)
Hello, please look at syntax and tell me why I am unable to restore. I recieved a backup from another server that I am trying to restore on a dev box:RESTORE FILELISTONLY
FROM DISK = 'C:\file_name.bak'
Returns this
Logical Name Physical Name
ICE_SampleC:\MSSQL\DATA\ICE_Sample.mdf
ICE_Sample_logC:\MSSQL\DATA\ICE_Sample_log.ldf
RESTORE DATABASE IDMine
FROM disk = 'C:\filename.bak',
MOVE 'ICE_Sample' to 'C:\ICE_Sample.mdf',
MOVE 'ICE_Sample_log' to 'C:\ICE_Sample_log.ldf',
WITH recovery
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ICE_Sample'.
Msg 319, Level 15, State 1, Line 5
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.
I believe that your MOVE satatements need to go after the WITH:
RESTORE DATABASE IDMine
FROM disk = 'C:\filename.bak'
WITH recovery,
MOVE 'ICE_Sample' to 'C:\ICE_Sample.mdf',
MOVE 'ICE_Sample_log' to 'C:\ICE_Sample_log.ldf'
Try that and see if it helps.
January 11, 2011 at 4:32 pm
Thanks for replying. No, this didn't work:
RESTORE DATABASE IDMine
FROM disk = 'C:\filename.bak',
WITH recovery,
MOVE 'ICE_Sample' to 'C:ICE_Sample.mdf',
MOVE 'ICE_Sample_log' to 'C:\ICE_Sample_log.ldf'
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 3
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.
January 11, 2011 at 5:24 pm
From BOL:
--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <service_broker_WITH options>
| , <point_in_time_WITH_options—RESTORE_DATABASE>
} [ ,...n ]
]
[;]
You will notice that there is no comma after the FROM. You separate the WITH statements with a comma...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2011 at 5:37 pm
Like this? I get new error.
RESTORE DATABASE IDMine
FROM disk = 'C:\filename.bak'
WITH recovery
,MOVE 'ICE_Sample' to 'C:\ICE_Sample.mdf'
,MOVE 'ICE_Sample_log' to 'C:\ICE_Sample_log.ldf'
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 10.50.1600.
That version is incompatible with this server, which is running
version 10.00.1600. Either restore the database on a server that
supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
January 11, 2011 at 7:29 pm
Yes - that is correct. Now, the problem you are having is that the backup was performed from SQL Server 2008 R2 and you are running SQL Server 2008.
You cannot restore a higher version to a lower version. You will need to upgrade or install SQL Server 2008 R2 in order to restore that database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 12, 2011 at 1:39 pm
Jeffrey, thanks.
Do you happen to know if SQL Server 2008 Eval edition is upgradeable to the R2 edition, without uninstall/install?
I have found an iso image of it...but if the knowledge is already out there....
see nothing on line about it.
January 12, 2011 at 1:47 pm
this says no: http://msdn.microsoft.com/en-us/library/ms143393.aspx
January 12, 2011 at 2:11 pm
OK, much thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply