An instance of SQL Server may not start successfully after you restore the model database by using the WITH NORECOVERY option (822852)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0

SYMPTOMS

When you try to start an instance of SQL Server after you restore the model system database by using the WITH NORECOVERY option, the instance of SQL Server may not start successfully. Additionally, you may notice the following error messages in the SQL Server error log files:

2003-04-18 09:37:38.85 spid5 Starting up database 'model'.
2003-04-18 09:37:39.24 spid5 Bypassing recovery for database 'model' because it is marked IN LOAD.
2003-04-18 09:37:39.65 spid5 Database 'model' cannot be opened. It is in the middle of a restore.

CAUSE

When you restore the model system database by using the WITH NORECOVERY option, the restore leaves the model database in an unrecovered state. Therefore, the model database can no longer be used as expected.

RESOLUTION

To resolve this problem, clear the "Loading" status of the model database in the instance of SQL Server. To do this, follow these steps:
  1. Click Start, click Run, type cmd, and then click OK.
  2. To start an instance of SQL Server and to recover only the master database, type or paste the following command at the command prompt, and then press ENTER:

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

    The following table lists the command-line parameters that are used in this command.

    Command line parameterUse
    -cForces an instance of SQL Server not to start as a service.
    -mAllows updates to the system catalog.
    -T3608Recovers the master database only.
    -T4022 Disables the startup procedures.


    After you run this command, you may notice the following messages in the SQL Server error log files:

    2003-04-18 09:49:14.61 spid3 Warning ******************
    2003-04-18 09:49:14.63 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
    2003-04-18 09:49:14.65 spid3 Recovering only master database
    2003-04-18 09:49:20.50 server SQL Server is ready for client connections

    Note Do not close the Command Prompt window.
  3. Start SQL Query Analyzer.

    Note The instance of SQL Server starts in single user mode. Therefore, only one connection can be established with the instance of SQL Server.
  4. Run the following SQL script in SQL Query Analyzer.

    Warning Updating system tables incorrectly can cause serious problems that may require you to reinstall SQL Server. Microsoft cannot guarantee that problems that result from the incorrect updating of system tables can be solved. Update system tables at your own risk.
    BEGIN TRANSACTION
      UPDATE sysdatabases SET status = <value> WHERE name = 'model'
    Note For a Microsoft SQL Server 2000 instance, replace value with 16.

    For a Microsoft SQL Server 7.0 instance, replace value with 0.
  5. If you notice following message after you run the SQL script that is in step 4, commit the transaction:
    (1 row(s) affected)

    To commit the transaction, run the following Transact-SQL command in SQL Query Analyzer:
    COMMIT TRANSACTION
    If you notice that more than one row is affected, roll back the transaction by running the following Transact-SQL command in SQL Query Analyzer:
    ROLLBACK TRANSACTION
  6. Stop the instance of SQL Server by pressing CTRL+C, type Yes at the command prompt, and then press ENTER.
  7. Restart the instance of SQL Server from SQL Server Service Manager.

MORE INFORMATION

For an instance of SQL Server to start successfully, the three system databases must be restored successfully. The three system databases are the master database, the model database, and the tempdb database. The model database is used as a template to create the tempdb system database. If the model database is not recovered successfully, SQL Server cannot create the tempdb database, and the instance of SQL Server does not start successfully.

When you restore a SQL Server database by using the WITH NORECOVERY option, the database remains in a non-operational state even after the restore operation is completed. SQL Server leaves the database in a non-operational state after a restore operation is completed so that additional transaction log backups can be restored. In such a scenario, the database is marked "Loading" after the restore operation is completed. Unless the "Loading" status is cleared, the SQL Server database cannot be used.

For a user database in an instance of SQL Server, you can clear the "Loading" status by running the following Transact-SQL statement in SQL Query Analyzer: 
RESTORE DATABASE <Database name> WITH RECOVERY

However, if the model system database has a "Loading" status, the instance of SQL Server will not start as expected, and you cannot run the specified Transact-SQL statement to clear the "Loading" status. To prevent this problem, we recommend that you restore the model database by using the WITH RECOVERY option. If you do this, the model database will be operational after the restore operation is completed.

By default, the RESTORE command uses the WITH RECOVERY option in SQL Server. The RESTORE command in SQL Server does not leave the database non-operational in a "Loading" status unless you explicitly specify this. However, there are some third-party restore solutions that use the WITH NORECOVERY option by default. Use of the WITH NORECOVERY option leaves the database non-operational. Therefore, if you use a third-party restore solution and you decide to restore the model database, make sure that you restore the model database by using the WITH RECOVERY option.

Note that after the model database is restored by using the WITH NORECOVERY option, the instance of SQL Server continues to run without any problem. However, when you restart the instance of SQL Server, the problem that is mentioned in the "Symptoms" section occurs. Therefore, we suggest that you check the SQL Server error log files every time that a database is restored. Although the SQL Server error log files do not specify the recovery option that is being used during a restore operation, the information can help you confirm that the model database was restored successfully. After a successful restore operation, you may notice a message that is similar to the following in the SQL Server error log files:

2003-06-18 06:51:15.42 backup Database restored: Database: model, creation date(time): 2003/01/09(11:16:43), first LSN: 5:161:1, last LSN: 5:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\model.bak'}).

REFERENCES

For more information about the restore operation in SQL Server, see the following topic in SQL Server Books Online:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_25rm.asp
Modification Type:MinorLast Reviewed:11/29/2005
Keywords:kbState kbDatabase kberrmsg kbprb KB822852 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.