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.