How to solve if SQL database struck in Restoring state

Ajit kumar G

534

Like ( 0 )

Report

Sometimes, even during a simple task like Database backup and restore could be tricky because of a strange error. That is how IT works.

During one such operation, I have observed that the Database was struck in Restoring state. And no SQL operations were working on the DB with the usual error message: "The database is not accessible for the user" and "The database is currently in use".

If you have a database backup, which most probably you will have if you were trying to restore the database - you can follow the below steps as a solution:

Execute the dbcc detachdb ('Your database name')

The observation made here is that, the GUI way to detach the database does not work. However, when you run this command - you will receive an error message when you execute. However the database is dropped which gives you a scope to attach the database again.
Now use the database backup with you to restore the database again. Maybe try to use RECOVERY while restoring, just to be sure.

If you are wondering what is the difference between RECOVERY and NORECOVERY,

Restoring a database with RECOVERY (also is used by default, if you don't specify explicitly to use NORECOVERY). RECOVERY key ensures that the database is available and online for use for the users after completion of a successful restore.

RESTORE DATABASE YourDatabase FROM DISK = 'C:\\Data\\YourDatabaseBackup.bak'
WITH RECOVERY
GO
Restoring a database with NORECOVERY is especially used when multiple backups are to be restored. So for all the restore backup commands we include NORECOVERY key and this ensures that the databases is not released to users until the last backup in the sequence is restored to the database. And with the last backup the RECOVERY key is used and database goes online.

RESTORE DATABASE YourDatabase FROM DISK - 'C:\\Data\\Backup_part1.bak'
WITH NORECOVERY
GO

RESTORE LOG YourDatabase FROM DISK = 'C:\\Data\\BackupLog-part2.trn'
WITH RECOVERY
GO