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

This sounds as though you are performing a DATABASE RESTORE whilst using the NO RECOVERY option. The reason you would want to do this is if you were planning to apply subseuqent transaction log backups after the initial restore.

If you only wish to restore a single database backup then remove the NO RECOVERY clause.

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_description
    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')