none
Rebuilding Sql Server 2008 from backups of system databases....

    Question

  • Experiment: I setup Sql server in a testlab, copied the system DB mdfs\ldfs and user mdf\ldf to a new folder, stopped sql server and deleted the original system databases. Sql Server will no longer start.  My goal is to get Sql Server running by using only the sql backups I made. All the documentation I find says that to restore the master database ("step 1") I need to start Sql server in single user mode. In my case sql server will not start at all (in any mode) as the system databases are missing.  How then do I "bootstrap" sql server into the running state?

    TIA,

    edm2

    Tuesday, July 30, 2013 4:11 AM

Answers

  • Hi,

    Rebuild the system databases and then restore those system databases using your backup files.

    To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

    1. Click Start, click Run, type cmd, and then click OK.
    2. Run the following command to rebuild the system databases:

    start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=Instance_Name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

    For example:

    start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

    Regards

    Kiran

    Tuesday, July 30, 2013 5:11 AM
  • Experiment: I setup Sql server in a testlab, copied the system DB mdfs\ldfs and user mdf\ldf to a new folder, stopped sql server and deleted the original system databases. Sql Server will no longer start.

    TIA,

    edm2

    I doubt in this case SQL server will restart,never tried deleting system databases

     First,if you delete system databses how will in any case your SQL server will restart ,and how will you restore backup

    It should have been that you should have tried restoring master DB in single user mode without deleting it.

    Also restore of system DB is only possible if both, backup of master db and master DB on which ur trying to restore is at same level .If your backup is at lower SP level (even though both are of same version like 2008) restore wont be successful.

    what Ganesh is suggesting is to rebuild it from SQL server installable.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Tuesday, July 30, 2013 5:45 AM

All replies

  • Go through below link for moving system databases

    http://support.microsoft.com/kb/224071

    Tuesday, July 30, 2013 4:21 AM
  • I don't think that approach will work in my situation. In my test I don't even have the mdfs\ldefs, only sql backups (".bak") of those databases. The Sql server engine can not presently be started and my goal is to get it started using from the backup files. 

    edm2

    Tuesday, July 30, 2013 4:43 AM
  • Hi,

    Rebuild the system databases and then restore those system databases using your backup files.

    To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

    1. Click Start, click Run, type cmd, and then click OK.
    2. Run the following command to rebuild the system databases:

    start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=Instance_Name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

    For example:

    start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

    Regards

    Kiran

    Tuesday, July 30, 2013 5:11 AM
  • Experiment: I setup Sql server in a testlab, copied the system DB mdfs\ldfs and user mdf\ldf to a new folder, stopped sql server and deleted the original system databases. Sql Server will no longer start.

    TIA,

    edm2

    I doubt in this case SQL server will restart,never tried deleting system databases

     First,if you delete system databses how will in any case your SQL server will restart ,and how will you restore backup

    It should have been that you should have tried restoring master DB in single user mode without deleting it.

    Also restore of system DB is only possible if both, backup of master db and master DB on which ur trying to restore is at same level .If your backup is at lower SP level (even though both are of same version like 2008) restore wont be successful.

    what Ganesh is suggesting is to rebuild it from SQL server installable.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Tuesday, July 30, 2013 5:45 AM
  • Interesting. I gather that "rebuilding" the system databases installs a "default" set of systems databases that sql can be started with. . It will be interesting to see where those "default" DBs come from. (i.e. I may need to keep the Sql install ISO nearby. Maybe not, I'll find out.)

    edm2

    Tuesday, July 30, 2013 5:49 AM