Restoring master to a different location

• carloarango@hotmail.com

  SSChasing Mays

  Points: 628

  More actions

  August 20, 2007 at 2:30 pm

  #197355

  I am trying to restore master to a new server but location of data and log files is different in this new server. The previous location was 'D:\Program Files\Microsoft SQL Server\MSSQL\data\" the current is in the C: Drive when trying to restore using MOVE:

  RESTORE DATABASE master FROM DISK ='\\Pfileserver\sqlbackup\MDFfiles\master_db_200708170121.BAK' WITH RECOVERY ,

        MOVE 'master' TO 'C:\Program Files\Microsoft SQL Server\data\master.mdf',

        MOVE 'mastlog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf'

  I get the following error message:

  The system database cannot be moved by RESTORE

  Is there a way you can restore master to a different location?

  Thanks,

  Carlos

• Kevin West-470850

  Newbie

  Points: 5

  More actions

  August 20, 2007 at 9:38 pm

  #728171

  Hi Carlos,

   

  Use the dettach function.

  1. Make a backup of the database.
  2. In the Microsoft SQL Server Management Studio, open databases, highlight the database you wish to move.
  3. Right click - select Tasks - Detach...
  4. Once the database is dettached, copy the .DAT and .LOG files from your D:\Program Files\Microsoft SQL Server\MSSQL\data\ to your new location i.e. C:\...
  5. In Microsoft SQL Server Management Studio right click on the Databases folder and select Attach - press the Add button and select the .DAT file in the new c:\ path.
  6. The database will be restored pointing to the new C:\ drive

  

• Deepak-405225

  SSCertifiable

  Points: 6090

  More actions

  August 21, 2007 at 3:43 am

  #728224

  If you want to move your master which is currently in D:\drive to C:\drive you need to perform as shown in the below link,

  http://sql-articles.com/articles.php?pageNum_alltop=5&totalRows_alltop=35

  just click on the topic "Moving system databases" you can see a detailed document of how to proceed........well i dont think you can perform as said by Kevin..........it will work perfectly for user db but not for system db.....for system db you need to stop sql services and move the path og masters ldf and mdf to a new location and go to sql server configuration manager and give the new path for mdf and ldf files in the startup parameters and then restart sql services..............

  [font="Verdana"]- Deepak[/font]