1

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

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
3
  • 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
    Commented Feb 19, 2013 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
    Commented Feb 19, 2013 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
    Commented Feb 19, 2013 at 8:31
1 
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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