Checking If Database In Restoring State

15
votes

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.

4
sql-servertsql

4 Answers

30
votes

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.

3
votes

SELECT DATABASEPROPERTYEX ('MyDb', 'Status')

1
votes

Others had a similar problem doing a RESTORE with pyodbc. My variation of the problem (with similar symptoms to yours) turned out to be an incorrect bak file. This can be discovered with the following T-SQL, looking for incorrect .mdf or .ldf file names or database names:

RESTORE FILELISTONLY FROM DISK = N'C:\dbname.bak'
1
votes

Method 2:

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

It is give me exact result.