Checking If Database In Restoring State

Question

I'm running a T-SQL script that drops a database and then restores it. The script runs against a SQL Server 2008 database. Sometimes there is a problem with the backup file and the database gets stuck in the restoring state.

IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = 'dbname')
BEGIN
    ALTER DATABASE [dbname]
    SET SINGLE_USER WITH 
    ROLLBACK IMMEDIATE
END

IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = 'dbname')
BEGIN
    DROP DATABASE [dbname]
END

RESTORE DATABASE [dbname]
FROM  DISK = N'C:\dbname.bak'
WITH  FILE = 1,
NOUNLOAD,
STATS = 10

The next time the script runs the script generates the error message

ALTER DATABASE is not permitted while a database is in the Restoring state.

What is the best way to check if the database is in the restoring state before trying to run the ALTER DATABASE command?

EDIT: The RESTORE DATABASE command that I'm running doesn't use the NO RECOVERY option.

Answer 1

It sounds as though you are performing a database restore with the NORECOVERY option. The reason you would want to do this is if you were planning to apply subsequent transaction log backups after the initial restore.

If you only wish to restore a single database backup then remove the NORECOVERY clause. If you are restoring transaction log backups, the final restore must be done without the NORECOVERY clause or if the last was applied with NORECOVERY you can RESTORE DATABASE DbName WITH RECOVERY to finalize.

To answer your question:

Method 1

SELECT DATABASEPROPERTYEX ('DatabaseName', 'Status')

See SQL Server Books Online: DATABASEPROPERTYEX (Transact-SQL)

Method 2

Review the sys.databases system view in order to determine the current state of a database. For example:

SELECT
    state,
    state_desc
    FROM sys.databases
WHERE [name] = 'DatabaseName'

A state of 1 = RESTORING

See Sys.Databases for documentation regarding this system view.

Answer 2

SELECT DATABASEPROPERTYEX ('MyDb', 'Status')