Thursday, March 29, 2012

Database in 'In recovery' mode

Hi,
There was the most awful case. During a update of a DB there was a
switching-off of power supplies. After switch on of a server the base has
passed in mode 'Suspect'. To restore base by standard way it was not
possible. Substitution of files and had turned out to open changes of the
status of base data.
Has executed substitution of files of a database. Has successfully opened a
database, structure - all is accessible.
Check of a file of a database on integrity is executed successfully, errors
are not present (DBCC CHECKDB).
I can not create backup, gives out the message that are opened deferred
transactions.
Also, at change of some objects there is a lag of a server that leads to
change of the status of database 'In Recovery'. In 2 and more hours the
status has not changed.
Whether it is necessary to wait for restoration? How to commit deferred tran?
Thank you for any help.
p.s. Backups very old :(Not sure if this is going to work ... a little bit risky but worth a try (I
guess)...
It seemed that some transaction are not clearing so try to rollback the
transaction and then try to backup the database and then try to turn the
database back online, so the command would look like:
USE master
GO
-- First set it to read only and rollback any active transaction
ALTER DATABASE [DatabaseName]
SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO
-- Backup the database, hopefully it should work since there should not be
any active transaction
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePathAndName]'
WITH INIT
GO
-- Turn the database back online
ALTER DATABASE [DatabaseName]
SET ONLINE WITH ROLLBACK IMMEDIATE
GO
Hopefully that would work (but no guarantee...)
Lucas
"int64" wrote:
> Hi,
> There was the most awful case. During a update of a DB there was a
> switching-off of power supplies. After switch on of a server the base has
> passed in mode 'Suspect'. To restore base by standard way it was not
> possible. Substitution of files and had turned out to open changes of the
> status of base data.
> Has executed substitution of files of a database. Has successfully opened a
> database, structure - all is accessible.
> Check of a file of a database on integrity is executed successfully, errors
> are not present (DBCC CHECKDB).
> I can not create backup, gives out the message that are opened deferred
> transactions.
> Also, at change of some objects there is a lag of a server that leads to
> change of the status of database 'In Recovery'. In 2 and more hours the
> status has not changed.
> Whether it is necessary to wait for restoration? How to commit deferred tran?
> Thank you for any help.
> p.s. Backups very old :(
>

No comments:

Post a Comment