locked
Restore Model system Database in Sql Server 2008/R2 RRS feed

  • Question

  • Dear Gurus,

    I'm a new bie to MS-SQl server. I'm trying to restore "Model" system database from the backup, but ending up with error.

    I used /m /t3608 and /t3609.

    Could you please help me in fixing the issue?

    Regards,

    Sunil


    Sunil

    Wednesday, August 15, 2012 5:55 PM

Answers

  • Hi Sunil,

    Thanks for the updating.

    Did you delete the model.mdf and modellog.ldf?

    The only database that can be restored in single user mode is the master database. If you deleted the model database files, SQL Server will not start as it cannot re-create the TEMPDB. If the original TEMPDB files are available, you can try the below steps:

    1. Copy them and rename them to model.mdf and modelog.ldf
    2. Restart SQL Server service normally from services.msc (leave agent and all other services stopped)
    3. Open command prompt and restore model from the backup

    For more details, please refer to this thread.


    Best Regards,
    Ray Chen

    • Marked as answer by Olaf HelperMVP Thursday, February 21, 2013 5:27 PM
    Monday, August 20, 2012 8:27 AM

All replies

  • Hi Sunil,

    Please check my blog for restoring System databases in 2 Simple Steps

    http://sqlservr.blog.com/2012/05/09/how-to-restore-system-database-in-2-steps/


    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, August 15, 2012 6:43 PM
  • Thank you Ramesh,

    Restoring of Model Ssystem database after connecting with -m option is working fine. But for Sql Server 2008 / 2008 R2 it's terminating the connection after we fired the restore command for Model database and instance is terminating.

    Just want to double check with you, that the link you provided is for Master, but have you tried to restore Model database in Sql Server 2008/R2.


    Sunil

    Thursday, August 16, 2012 4:36 AM
  • Hi Sunil,

    tested it on my desktop and it is working fine, check the scren shot below


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, August 16, 2012 6:50 AM
  • Thank you Vasu,


    Will try it. This is for Sql Server 2008 / 2008 R2 right?


    Sunil

    Thursday, August 16, 2012 7:21 AM
  • Hi Vasu,

    I tried on my machine with the steps from your blog. I ended up with the following error:

    HResult 0x6D, Level 16, state 1

    Shared memory Provider: The Pipe has been ended.

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0: Communication Link failure error.

    Moreover, I don't have any .mdf or .ldf files related to Model database. I was trying to restore model database from a backup.

    We are using 64bit machine


    Sunil

    Thursday, August 16, 2012 8:54 AM
  • Hi Ramesh,

    Sorry. Mistyped your name.

    I tried on my machine with the steps from your blog. I ended up with the following error:

    HResult 0x6D, Level 16, state 1

    Shared memory Provider: The Pipe has been ended.

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0: Communication Link failure error.

    Moreover, I don't have any .mdf or .ldf files related to Model database. I was trying to restore model database from a backup.

    We are using 64bit machine


    Sunil

    Thursday, August 16, 2012 8:55 AM
  • Hi Sunil,

    Thanks for update.

    What error message did you encounter when you tried to restore Model database?

    According to your description, the command –m means “allows updates to the system catalog” and –t3608 means “recovers the master database only”. For more details, please refer to this article: An instance of SQL Server may not start successfully after you restore the model database by using the WITH NORECOVERY option.

    Are you performing any transactions against Model database? If so, you need to restore all subsequent log backup files followed by full restore on Model database. Here is a similar thread, please take a look at it as your reference.


    Best Regards,
    Ray Chen

    Friday, August 17, 2012 6:29 AM
  • Hi Chen,

    I'm not doing any transactions on Model database. The instance is pretty new.

    I tried with /t3608 and /t3609 individually. After starting the instance with these parameters, i used sqlcmd -s <instancename> -E

    then used restore database model from disk='<path>' with replace.

    Here, the instance is trying to create a new tempdb and failing with unable to find .mdf and .ldf files for Model database. The error is mentioned in my previous post "Connection Link Failure".

    I gone through the thread you mentioned, but I can't still make a restore successfully.


    Sunil

    Friday, August 17, 2012 4:20 PM
  • Hi Sunil,

    Thanks for the updating.

    Did you delete the model.mdf and modellog.ldf?

    The only database that can be restored in single user mode is the master database. If you deleted the model database files, SQL Server will not start as it cannot re-create the TEMPDB. If the original TEMPDB files are available, you can try the below steps:

    1. Copy them and rename them to model.mdf and modelog.ldf
    2. Restart SQL Server service normally from services.msc (leave agent and all other services stopped)
    3. Open command prompt and restore model from the backup

    For more details, please refer to this thread.


    Best Regards,
    Ray Chen

    • Marked as answer by Olaf HelperMVP Thursday, February 21, 2013 5:27 PM
    Monday, August 20, 2012 8:27 AM
  • Hi Ray Chen,

    Yes, you are correct. I deleted the model database .mdf and .ldf files to just mimic a restore in case of disk crash.

    I already tried  the steps that you highlighted. Thank you.

    But, the same scenario, worked for Sql Server 2005 when I lost my model database files and done a restored after starting the instance with /t3608(forgot actual options used).

    I could able to restore model database in sql 2005 without having the file on the disk.


    Sunil

    Monday, August 20, 2012 12:30 PM
  • Hi Sunil,

    Could you please just try “restore database model” without giving the path after you use sqlcmd –s <instancename> -E? For more details, please refer to this thread.


    Best Regards,
    Ray Chen

    • Proposed as answer by Shulei Chen Monday, August 27, 2012 9:51 AM
    • Unproposed as answer by Shulei Chen Monday, August 27, 2012 9:51 AM
    Tuesday, August 21, 2012 9:16 AM
  • Hi ray,

    Thank you...it's been so long and I appreciate that you came back to help me in fixing this.

    I'll try and update you on the same.

    Thanks again.

    Regards,
    Sunil


    Sunil

    Friday, February 22, 2013 8:59 AM