Hello,

our system:
MS SQL Server 2005, SP3

I suddenly got the following error in my SQL log:

The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
RESTORE DATABASE model
WITH RECOVERY

is not possible - or is there!?
Any ideas?

I also have a backup of all system db's.

Best regards
Svein Ingebrigtsen
Denmark

Hi
You will have to connect to SQL Server with single mode

C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

Then RESTORE model database WITH RECOVERY
  our system:
  MS SQL Server 2005, SP3

  I suddenly got the following error in my SQL log:

  The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
  2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
  2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

  I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
  RESTORE DATABASE model
  WITH RECOVERY

  is not possible - or is there!?
  Any ideas?

  I also have a backup of all system db's.

  Best regards
  Svein Ingebrigtsen
  Denmark

Hello Uri,

Done connecting to SQL Server in Singel mode.
How would you run the command; with SQLcmd and the SQL script, or?

Svein
  You will have to connect to SQL Server with single mode

  C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

  Then RESTORE model database WITH RECOVERY
    Hello,

    our system:
    MS SQL Server 2005, SP3

    I suddenly got the following error in my SQL log:

    The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
    2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
    2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

    I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
    RESTORE DATABASE model
    WITH RECOVERY

    is not possible - or is there!?
    Any ideas?

    I also have a backup of all system db's.

    Best regards
    Svein Ingebrigtsen
    Denmark

Svein

You can do that via SQLCMD or copy text RESTORE DATABASE model from disk .....WITH RECOVERY into .sql file and call this file

SQLCMD -E -Sservername -iC:\a.sql

  Hello Uri,

  Done connecting to SQL Server in Singel mode.
  How would you run the command; with SQLcmd and the SQL script, or?

  Svein
    Hi
    You will have to connect to SQL Server with single mode

    C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

    Then RESTORE model database WITH RECOVERY
      Hello,

      our system:
      MS SQL Server 2005, SP3

      I suddenly got the following error in my SQL log:

      The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
      2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
      2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

      I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
      RESTORE DATABASE model
      WITH RECOVERY

      is not possible - or is there!?
      Any ideas?

      I also have a backup of all system db's.

      Best regards
      Svein Ingebrigtsen
      Denmark

Hello Uri,
I have also tried starting the SQL Server service in Single user mode with the options: -c -m -T3608  - and it works fine.
The service starts and all seems ok.
But I am not able to connect with the the SQL Server Management studio!

When I try to connect via command line with "sqlservr -c -m -T3608  " I also get a connection, but I also get the following error:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establi
shing a connection to the server. When connecting to SQL Server 2005, this
failu
re may be caused by the fact that under the default settings SQL Server does
not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired

Maybe it's something to do with my connection string!?
Any ideas?

/Svein
  You can do that via SQLCMD or copy text RESTORE DATABASE model from disk ....WITH RECOVERY into .sql file and call this file

  SQLCMD -E -Sservername -iC:\a.sql

    Hello Uri,

    Done connecting to SQL Server in Singel mode.
    How would you run the command; with SQLcmd and the SQL script, or?

    Svein
      Hi
      You will have to connect to SQL Server with single mode

      C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

      Then RESTORE model database WITH RECOVERY
        Hello,

        our system:
        MS SQL Server 2005, SP3

        I suddenly got the following error in my SQL log:

        The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
        2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
        2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

        I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
        RESTORE DATABASE model
        WITH RECOVERY

        is not possible - or is there!?
        Any ideas?

        I also have a backup of all system db's.

        Best regards
        Svein Ingebrigtsen
        Denmark

Sorry - my previous post was incomplete:
  Hello Uri,
  I have also tried starting the SQL Server service in Single user mode with the options: -c -m -T3608  - and it works fine.
  The service starts and all seems ok.
  But I am not able to connect with the the SQL Server Management studio!

  I connect ok via command line with "sqlservr -c -m -T3608  ".
  But when I run a sql script as you suggested with SQLcmd I get the following error:

  HResult 0x2, Level 16, State 1
  Named Pipes Provider: Could not open a connection to SQL Server [2].
  Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
  establi
  shing a connection to the server. When connecting to SQL Server 2005, this
  failu
  re may be caused by the fact that under the default settings SQL Server does
  not
   allow remote connections..
  Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired

  Maybe it's something to do with my connection string!?
  Any ideas?

  /Svein
    Svein

    You can do that via SQLCMD or copy text RESTORE DATABASE model from disk ....WITH RECOVERY into .sql file and call this file

    SQLCMD -E -Sservername -iC:\a.sql

      Hello Uri,

      Done connecting to SQL Server in Singel mode.
      How would you run the command; with SQLcmd and the SQL script, or?

      Svein
        Hi
        You will have to connect to SQL Server with single mode

        C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

        Then RESTORE model database WITH RECOVERY
          Hello,

          our system:
          MS SQL Server 2005, SP3

          I suddenly got the following error in my SQL log:

          The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
          2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
          2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

          I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
          RESTORE DATABASE model
          WITH RECOVERY

          is not possible - or is there!?
          Any ideas?

          I also have a backup of all system db's.

          Best regards
          Svein Ingebrigtsen
          Denmark

Hello Uri,

I solved this through my backup solution "BackupExec 12.5".
Every time BackupExec backs up the system databases, it also makes copies:
master$4idr, mastlog$4idr, model$4idr,modellog$4idr

Deleted or renaming the corruptet ones and renaming these ones to master.mdb etc, removing "Read Only" and starting or restarting the SQL services is a solution.

This worked for me!

But thans a lot Uri - your help was deeply appriciated!

/Svein

  Sorry - my previous post was incomplete:
    Hello Uri,
    I have also tried starting the SQL Server service in Single user mode with the options: -c -m -T3608  - and it works fine.
    The service starts and all seems ok.
    But I am not able to connect with the the SQL Server Management studio!

    I connect ok via command line with "sqlservr -c -m -T3608  ".
    But when I run a sql script as you suggested with SQLcmd I get the following error:

    HResult 0x2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
    establi
    shing a connection to the server. When connecting to SQL Server 2005, this
    failu
    re may be caused by the fact that under the default settings SQL Server does
    not
     allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired

    Maybe it's something to do with my connection string!?
    Any ideas?

    /Svein
      Svein

      You can do that via SQLCMD or copy text RESTORE DATABASE model from disk ....WITH RECOVERY into .sql file and call this file

      SQLCMD -E -Sservername -iC:\a.sql

        Hello Uri,

        Done connecting to SQL Server in Singel mode.
        How would you run the command; with SQLcmd and the SQL script, or?

        Svein
          Hi
          You will have to connect to SQL Server with single mode

          C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

          Then RESTORE model database WITH RECOVERY
            Hello,

            our system:
            MS SQL Server 2005, SP3

            I suddenly got the following error in my SQL log:

            The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
            2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
            2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

            I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
            RESTORE DATABASE model
            WITH RECOVERY

            is not possible - or is there!?
            Any ideas?

            I also have a backup of all system db's.

            Best regards
            Svein Ingebrigtsen
            Denmark

Svein
I'm glad  you solved it.

1) Start SQL Server with single user mode -m
2) Open SSMS but do not connect to any instance, just a new query and then connect to the instance so then  you are able to run RESTORE


  Hello Uri,

  I solved this through my backup solution "BackupExec 12.5".
  Every time BackupExec backs up the system databases, it also makes copies:
  master$4idr, mastlog$4idr, model$4idr,modellog$4idr

  Deleted or renaming the corruptet ones and renaming these ones to master.mdb etc, removing "Read Only" and starting or restarting the SQL services is a solution.

  This worked for me!

  But thans a lot Uri - your help was deeply appriciated!

  /Svein

    Sorry - my previous post was incomplete:
      Hello Uri,
      I have also tried starting the SQL Server service in Single user mode with the options: -c -m -T3608  - and it works fine.
      The service starts and all seems ok.
      But I am not able to connect with the the SQL Server Management studio!

      I connect ok via command line with "sqlservr -c -m -T3608  ".
      But when I run a sql script as you suggested with SQLcmd I get the following error:

      HResult 0x2, Level 16, State 1
      Named Pipes Provider: Could not open a connection to SQL Server [2].
      Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
      establi
      shing a connection to the server. When connecting to SQL Server 2005, this
      failu
      re may be caused by the fact that under the default settings SQL Server does
      not
       allow remote connections..
      Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired

      Maybe it's something to do with my connection string!?
      Any ideas?

      /Svein
        Svein

        You can do that via SQLCMD or copy text RESTORE DATABASE model from disk ....WITH RECOVERY into .sql file and call this file

        SQLCMD -E -Sservername -iC:\a.sql

          Hello Uri,

          Done connecting to SQL Server in Singel mode.
          How would you run the command; with SQLcmd and the SQL script, or?

          Svein
            Hi
            You will have to connect to SQL Server with single mode

            C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

            Then RESTORE model database WITH RECOVERY
              Hello,

              our system:
              MS SQL Server 2005, SP3

              I suddenly got the following error in my SQL log:

              The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
              2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
              2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

              I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
              RESTORE DATABASE model
              WITH RECOVERY

              is not possible - or is there!?
              Any ideas?

              I also have a backup of all system db's.

              Best regards
              Svein Ingebrigtsen
              Denmark

Close SSMS and run

SQLCMD -E -Q"RESTORE DATABASE model FROM DISK='C:\Backups\model.bak'"


  Sorry - my previous post was incomplete:
    Hello Uri,
    I have also tried starting the SQL Server service in Single user mode with the options: -c -m -T3608  - and it works fine.
    The service starts and all seems ok.
    But I am not able to connect with the the SQL Server Management studio!

    I connect ok via command line with "sqlservr -c -m -T3608  ".
    But when I run a sql script as you suggested with SQLcmd I get the following error:

    HResult 0x2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
    establi
    shing a connection to the server. When connecting to SQL Server 2005, this
    failu
    re may be caused by the fact that under the default settings SQL Server does
    not
     allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired

    Maybe it's something to do with my connection string!?
    Any ideas?

    /Svein
      Svein

      You can do that via SQLCMD or copy text RESTORE DATABASE model from disk ....WITH RECOVERY into .sql file and call this file

      SQLCMD -E -Sservername -iC:\a.sql

        Hello Uri,

        Done connecting to SQL Server in Singel mode.
        How would you run the command; with SQLcmd and the SQL script, or?

        Svein
          Hi
          You will have to connect to SQL Server with single mode

          C:\Program Files\Microsoft SQL Server\MSSQL\BINN> sqlservr -c -m -T3608

          Then RESTORE model database WITH RECOVERY
            Hello,

            our system:
            MS SQL Server 2005, SP3

            I suddenly got the following error in my SQL log:

            The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
            2009-07-07 08:26:08.33 spid9s      Error: 927, Severity: 14, State: 2.
            2009-07-07 08:26:08.33 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

            I cannot connect to my SQL server with Enterprise manager, so the obvious restore mode with at SQL script:
            RESTORE DATABASE model
            WITH RECOVERY

            is not possible - or is there!?
            Any ideas?

            I also have a backup of all system db's.

            Best regards
            Svein Ingebrigtsen
            Denmark

drop table not showing up in the transaction log
deleting DB
strange BLOB beahaviour
AWE on SQL Server2005
ID large queries while they execute and kill
'Generate scripts' functionality in SQL 2005 not being consistent
Problem with SUM - Help please
problem with new login
Backups & Transaction Files
How best to move large databases?