CODEWITHSUNDEEP
sqlsql-server
adricadar
adricadar

Reputation: 10209

Identify when a database is in restore state

I'm restoring a database from backups in Sql Server Management Studio.

This is done in 2 states.

  1. Start Restore. I run a sql script to restore a database (ex: MyDb).
  2. Finish Restore. MyDb is restored

How can I identify when a database is in restore state (between 1 and 2)? I want to make an sql script that tell me that, if it's possible.

Note: I know i can see that from Sql Server Management Studio -> Activity Monitor.

Upvotes: 1

Views: 402

Answers (2)

Avi Turner
Avi Turner

Reputation: 10466

As @SchmitzIT has pointed out in his answer (+1), and as specified in MSDN, this data can be retrieved via querying sys.databases: 

SELECT * 
FROM sys.databases
WHERE state = 1

If your DB is hanging in RESTORING state, you can release it using:

RESTORE DATABASE 'DATABASE_NAME' WITH RECOVERY

Upvotes: 2

SchmitzIT
SchmitzIT

Reputation: 9552

You can query the sys.databases table:

SELECT state FROM sys.databases

A state of 1 indicates the database is restoring.

Source: MSDN

Upvotes: 3

Related Questions