Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

To be honest I'm not a DBA expert. I get asked by one of my clients in terms of moving a SQL Server development instance that is stored in the production server (source) to another server (destination)

The source server has two volumes E and F:

  • Volum E stores Databases (E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA)
  • Volum F stores Logs (F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data)

I have backed one database (Backup_Test.bak) for testing purpose in the source server and restored to the destination by using the following T-SQL via SQL Server Management Studio in the destination:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE,
MOVE 'Backup_Test' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA\Backup_Test.mdf',
MOVE 'Backup_Test_log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data\Backup_Test_log.ldf'

After executing, I get the error

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'MOVE'.

In the destination, someone installed SQL Server and locate all things stored in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER.

I'm suspecting that different location on both the source (E & F) and destination (only C) causes my issue. If so, do I have to re-install SQL Server on the destination?

The version on the source is SQL Server 2008 and on the destination is /R2. The named instance on the source is SQLSP1\SPDEV and on the destination is SQLSP2. Do I have to create an alias SQLSP2\SPDEV?

Many thanks for your help. I really appreciate.

Regards, -T.s

share|improve this question
add comment

2 Answers

You are not moving instances, you are copying databases via backup restore. All you need is to get the syntax right, as per RESTORE spec:

<general_WITH_options> [ ,...n ]::= 
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
          [ ,...n ] 

Each WITH option is separated from the preceding with a comma. What you're missing is the , and you need to move the files into the destination location:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE
 , MOVE 'Backup_Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test.mdf',
 , MOVE 'Backup_Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test_log.ldf'

Judging from your folder names (MSSQL10 vs. MSSQL10_50) your source server is SQL Server 2008 (not R2) and only the destination is SQL Server 2008 R2. Which means that the copy will upgrade the database and the database will never be possible to copy/restore on the original non-R2 instance.

share|improve this answer
    
Thanks. I've missed a comma after REPLACE. I have a further question. On the destionation, C:\SQL_Data stores all databases and C:\SQL_Logs stores all Logs. On the source, as you see in my question, E:\path\Data stores all databases and F:\path\Data\ stores all logs. Do I have to re-structure drives on the destination? Or my script above can be probably used in my case. Many thanks for help. –  Thuan Feb 19 '13 at 8:25
    
I've changed the path: Move 'Backup_Test' TO 'C:\SQL_DATA\Backup_Test.mdf' Move 'Backup_Test_log' TO 'C:\SQL_Logs\Backup_Test_log.ldf' The script is succesfully executed without any error message but I don't know if it's done. –  Thuan Feb 19 '13 at 8:27
    
if it executed w/o errors and finished it means is done. You should now have a database called Backup_Test on the destination which is a copy of the original. –  Remus Rusanu Feb 19 '13 at 8:31
add comment
RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE,
MOVE 'Backup_Test' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA\Backup_Test.mdf',
MOVE 'Backup_Test_log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data\Backup_Test_log.ldf'

Only you need add comma after REPLACE key

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.