• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

syntax using the move command

on a sql2000+sp4 server I am trying to restore from backup the master DB from one server to another, and in a different location on the recovery server, as part of a disaster recovery plan. Working the sql on the second server, I start up in single server mode (sqlservr -c -m) and try to run the following in query analyizer

RESTORE DATABASE MASTER
               FROM DISK = 'c:\sql_backup_test\mastermaster_db_200603270200.BAK',
               WITH MOVE  'MASTER'  TO  'c:\sql\mssql\data\master_mdf',
               MOVE 'MASTLOG' TO 'C:\sql\mssql\data\master.ldf,


It is givining me an error of incorrect syntax near the keyword 'with' on line 3 I am not a DB developer and not experienced with SQl. Any help would be appreciated.

0
gbird2005
Asked:
gbird2005
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
RESTORE DATABASE MASTER
               FROM DISK = 'c:\sql_backup_test\mastermaster_db_200603270200.BAK'
               WITH MOVE  'MASTER'  TO  'c:\sql\mssql\data\master_mdf',
               MOVE 'MASTLOG' TO 'C:\sql\mssql\data\master.ldf,
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ie the comme after the FROM DISK was too much
0
 
gbird2005Author Commented:
Thats sovled the sqyntax problem but I am getting the error that the system database can not be move by a restore command, which I thouhg you can do when in single user mode.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to move the master database, simply do the following steps:

* shut down sql server
* move the physical files
* change the sql server's registry settings that specify the master database file locations
* startup sql server

registry path:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\SQLINSTNAME\MSSQLServer\Parameters
0
 
ptjcbCommented:
0
 
gbird2005Author Commented:
excellent reference thank for all your help. consider this closed.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>consider this closed.

it's your job to close it. either accept a single comment as answer, or split the points using the split points link below the last comment (which I guess won't appear here as you need at least 60 points to split)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now