locked
Model DB restore Issue RRS feed

  • Question

  • Tried to do a couple of database restores using backup exec, and for some stupid reason I left the 'Model' db in the restore job. Now for some reason I can't start the SQL instance and get the following error

     

    Database 'model' cannot be opened. It is in the middle of a restore.
    2011-07-26 12:31:08.50 spid36s     Starting up database 'TimeAndAttendance'.
    2011-07-26 12:31:08.50 spid12s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

     

    Is there a way around this issue?

    Tuesday, July 26, 2011 2:46 AM

Answers

  • Dhrush , you have managed to get your model back , so not going to touch it in depth .

    To resore any database you need tempdb online .This is because there are some commands that run in tempdb when the DBs are restored .As per sequence as well you need to recover tempdb after master is online ..

    Now Traceflag 3608 will skip recovery of all the databases except master .But that is not sufficient to restore Model . You also have to make sure that you use flag -m with T3608 .Once you have recovered master you can recover tempdb by using dbcc dbrecover (tempdb) . After that you can restore model .In case it gets stuck due to unknown reasons , Dinesh already gave you the idea ...

    Now if you do not want to recover Tempdb and still want to restore Model use trace flag -m with T2609 or just use sp_attachdb as it does not need tempdb ..

    FOr your user database take it to Emergency mode and run checkdb ...


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, July 27, 2011 7:57 AM
  • For 2005 and above : alter database <dbname> set emergency

    For 2000 : update sysdatabases set status =32768 where dbid= <dbid> (make sure allow updates in sp_configure is set to 1)


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, July 28, 2011 6:47 AM

All replies

  • Hi,

    Since it is a model database you are are not in big trouble,fallow below steps

    1.Stop SQL Service

    2.Start SQL service passing by T3608 (it will avoid all dbs recovery process except master db)

    net start mssqlserver /T3608--for default instance

    netstart <mssql$instancename> /T3608--for named instance

    3.After success ful restart ,use below command in new query window or in SQL CMD

    Restore database Model with recovery

    or

    Restore database Model from disk ='path\model.bak'

    This will bring Model online .Issue will get resolve


    Sivaprasad.L Together We can Achieve
    Tuesday, July 26, 2011 3:32 AM
  • I've even tried to rebuild the sys db's but get this errror

    Overall summary:
      Final result:                  Failed: see details below
      Exit code (Decimal):           -2068643838
      Exit facility code:            1203
      Exit error code:               2
      Exit message:                  The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.
      Start time:                    2011-07-26 13:37:49
      End time:                      2011-07-26 13:38:12
      Requested action:              RebuildDatabase

    Tuesday, July 26, 2011 4:00 AM
  • Thanks for the reply, I have actually tried both those methods, but alas they both fail

    When trying to restore from disk I get this error

    1> restore database Model from disk ='C:\model.bak'
    2> go
    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 fail
    ure.

     

    or from Recovery I get

    Msg 4333, Level 16, State 1, Server BALL-SVR10, Line 1
    The database cannot be recovered because the log was not restored.
    Msg 3013, Level 16, State 1, Server BALL-SVR10, Line 1
    RESTORE DATABASE is terminating abnormally.

     

    The log file after trying to start SQL service after these is

    2011-07-26 14:21:30.84 spid13s     The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
    2011-07-26 14:21:30.84 spid13s     Error: 927, Severity: 14, State: 2.
    2011-07-26 14:21:30.84 spid13s     Database 'model' cannot be opened. It is in the middle of a restore.
    2011-07-26 14:21:30.84 spid13s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
    2011-07-26 14:21:30.84 spid13s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
    2011-07-26 14:21:30.84 spid50s     Error: 824, Severity: 24, State: 6.

    Tuesday, July 26, 2011 4:22 AM
  • Hi,

    I guess you are having transactional log backup enabled for model database also.Are you performing any transactions against model database ?

    If yes you need to restore all subsequent log backup files fallowed by full restore on model database.

     


    Sivaprasad.L Together We can Achieve
    Tuesday, July 26, 2011 4:27 AM
  • Hi Dursh,

    I Assume It's SQL 2008! Make sure that you are choosing recovery and replace switches while restoring the database. 

    Eg: RESTORE DATABASE MODEL

    FROM DISK = 'blabla\model.bak'

    WITH REPLACE,RECOVERY

     

    Also, Make sure that the mdf and ldf file locations of the System Databases are intact and double check Free space available on your LUNS.

    If nothing works, try to start SQL Server in minimal Configuration mode using -m startup parameter and connect using SQLCMD to verify what's happening. 
    let me know if it helps. 

     

    -Sreekanth Bandarla


    Sreekanth Bandarla MCITP - SQL Server 2008 Database Administrator.
    Tuesday, July 26, 2011 4:48 AM
  • Hi,

    -m start up parameter is for single user mode ,To start sql server in minimum configuration mode you should use -f parameter.

    Take it as positive.


    Sivaprasad.L Together We can Achieve
    Tuesday, July 26, 2011 5:31 AM
  • Hey guys,

     

    I have tried all the above mentioned methods but it still doesn't work..

    Hell, I've tried to remove the SQL isntace and start from scratch again and simply restore the master/msdb backups but to just rub salt in the wound the uninstall fails also with a 'Removal architecture mismatch' "The CPU architectures of features you selected to removed are different then Setup"

    Tuesday, July 26, 2011 5:57 AM
  • In case you the uninstall failed initially, and you still in the same state I would suggest you start SQL Server with -T3608 and run the below command:
    Restore database Model with recovery.

     

    HTH


    Dinesh B M [MSFT] -- This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 26, 2011 7:46 AM
  • Oops! My Bad.

    Recalled this "-f" guy also puts SQL in Single user mode besides starting in minimal Config mode.  Thanks Siva for correcting me!


    Sreekanth Bandarla MCITP - SQL Server 2008 Database Administrator.
    Tuesday, July 26, 2011 11:42 AM
  • Did you said, you also tried Rebuilding your Master Database from setup.exe?
    Sreekanth Bandarla MCITP - SQL Server 2008 Database Administrator.
    Tuesday, July 26, 2011 11:43 AM
  • Well this has all just gone to crap... I have no idea how this managed to land on my lap to deal with..


    Either way, I've managed to get the model db to come out of recovery mode and the SQL instance starts, however the DB's are all corrupted now and of course these guys abviously have never heard of backups..

    The good thing is 99.9% of the data in the DB's is useless testing crap but there are a few SP and tables that I need to get out.. Is there a really good tool that can somehow allow my get these few things out of the DB's?

    Tuesday, July 26, 2011 11:20 PM
  • Hi,

    If your User DBs are having sps then try

    Try to make one after one user DB in to emergency mode, pull the data from tables using ssis

    for stored procs try sp_help text and save the out put(you can generate script also in management studio)

    hope your sps are not encrypted by passwords :)


    Sivaprasad.L Together We can Achieve
    Wednesday, July 27, 2011 12:36 AM
  • Well I persume that the DB's are cactus... The error in the SQL log is

     

    2011-07-26 12:27:02.28 spid50s     SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfd23fdfa; actual: 0xc19c4fbe). It occurred during a read of page (1:0) in database ID 39 at offset 0000000000000000 in file 'D:\SQLData\RMS.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    So do do you think the emergency mode would work on these?

    Wednesday, July 27, 2011 5:04 AM
  • Dhrush , you have managed to get your model back , so not going to touch it in depth .

    To resore any database you need tempdb online .This is because there are some commands that run in tempdb when the DBs are restored .As per sequence as well you need to recover tempdb after master is online ..

    Now Traceflag 3608 will skip recovery of all the databases except master .But that is not sufficient to restore Model . You also have to make sure that you use flag -m with T3608 .Once you have recovered master you can recover tempdb by using dbcc dbrecover (tempdb) . After that you can restore model .In case it gets stuck due to unknown reasons , Dinesh already gave you the idea ...

    Now if you do not want to recover Tempdb and still want to restore Model use trace flag -m with T2609 or just use sp_attachdb as it does not need tempdb ..

    FOr your user database take it to Emergency mode and run checkdb ...


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, July 27, 2011 7:57 AM
  • Was wondering if someone had a link to the best way to take the SQL server to 'Emergency Mode'??
    Wednesday, July 27, 2011 10:31 PM
  • For 2005 and above : alter database <dbname> set emergency

    For 2000 : update sysdatabases set status =32768 where dbid= <dbid> (make sure allow updates in sp_configure is set to 1)


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, July 28, 2011 6:47 AM