Microsoft KB Archive/822852

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

Article ID: 822852

Article Last Modified on 5/12/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



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 &quot;Loading&quot; status of the model database in the instance of SQL Server. To do this, follow these steps:  Click Start, click Run, type cmd, and then click OK. 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.

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. 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.  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 = WHERE name = 'model' Note For a Microsoft SQL Server 2000 instance, replace  with 16.

For a Microsoft SQL Server 7.0 instance, replace  with 0.   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  Stop the instance of SQL Server by pressing CTRL+C, type Yes at the command prompt, and then press ENTER. Restart the instance of SQL Server from SQL Server Service Manager.</li></ol>

<div class="moreinformation_section">

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 &quot;Loading&quot; after the restore operation is completed. Unless the &quot;Loading&quot; status is cleared, the SQL Server database cannot be used.

For a user database in an instance of SQL Server, you can clear the &quot;Loading&quot; 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 &quot;Loading&quot; status, the instance of SQL Server will not start as expected, and you cannot run the specified Transact-SQL statement to clear the &quot;Loading&quot; 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 &quot;Loading&quot; 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 &quot;Symptoms&quot; 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'}).

<div class="references_section">