none
NEWBIE: Can I restore a database (master) like this? RRS feed

  • Question

  • Hi,

    We take a full backup of our database (2008R2). When I restore it I want to change the disk where the master db resides and the other system db:s.

    Is it possible to move the master (and the others) db doing it like that?

    Regards

    H

    Wednesday, April 18, 2012 6:11 AM

Answers

  • Hi,

    Why do you want to do it this way? You would be better served to move the system databases using the method that MS suggest. Once you do this then when you back them up the backup takes the new location into account. You can't move the hidden resource database.

    http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Hristo77 Wednesday, April 18, 2012 11:21 AM
    Wednesday, April 18, 2012 9:17 AM
  • It depends on different database.

    For master , you cannot because you need to change the startup path from configuration manager for the master.mdf and ldf files.

    So i think you need to restore like normal, then change these configurations. Check moving master database in this link

    for model database also you cannot move files during restore. However moving files for model  is little different, check the same link there is an option to move the files.

    For msdb if you have your agent service stopped, then you can restore with the move option.

    for tempdb you cannot restore you can only move the files.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by Hristo77 Wednesday, April 18, 2012 11:21 AM
    Wednesday, April 18, 2012 9:25 AM

All replies

  • Hi,

    Why do you want to do it this way? You would be better served to move the system databases using the method that MS suggest. Once you do this then when you back them up the backup takes the new location into account. You can't move the hidden resource database.

    http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Hristo77 Wednesday, April 18, 2012 11:21 AM
    Wednesday, April 18, 2012 9:17 AM
  • It depends on different database.

    For master , you cannot because you need to change the startup path from configuration manager for the master.mdf and ldf files.

    So i think you need to restore like normal, then change these configurations. Check moving master database in this link

    for model database also you cannot move files during restore. However moving files for model  is little different, check the same link there is an option to move the files.

    For msdb if you have your agent service stopped, then you can restore with the move option.

    for tempdb you cannot restore you can only move the files.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by Hristo77 Wednesday, April 18, 2012 11:21 AM
    Wednesday, April 18, 2012 9:25 AM
  • Hi,

    follow below steps

    1) restore teh backup .

    2) stop SQL server service

    3) Open Regedit

     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters

     and change the path for SQLArg0, SQLArg1 and SQLArg2 as per your requiremnet  ( newly copied location of Master )

    4) Start SQL server service

    All done


    Prashant Deshpande

    Wednesday, April 18, 2012 11:55 AM