How to change database status is that stays..... "restoring"

Question

How to change database status is that stays..... "restoring"

JMSM

SSCertifiable

Points: 5440
More actions
December 18, 2009 at 5:43 am

#213817

Hello,
Can anybody help me please.
I've got one database that stays in Restoring Status after a restore is finished.
I've run the following comand but nothing happens.
Can anyone help me?
Thanks and regards,
JMSM 😉
USE Master
GO
-- Enable system changes
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 24
WHERE [Name] = 'MyDatabaseName'
GO
-- Disable system changes
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Good thing that you're not running SQL 2000 (at least I assume you're not) or that could have done nasty things to the database. If you are using SQL 2000, drop that database and restore again from scratch to ensure there are no lingering side-effects

The system tables cannot be updated in SQL 2005/2008, for good reason. They should not be touched unless you're completely happy with the possibility of having to restore the DB that you worked on.

I'm guessing that when you did the restore you either specified the NORECOVERY option or, if it was restored via a GUI you selected an option that said something about restoring more backups. In either case, the DB will restore and be left in the recovering state so that you can restore diff or log backups.

Either restore those additional backups and use the WITH RECOVERY option on the last one or run this

RESTORE DATABASE <db name> WITH RECOVERY

If that doesn't work, post the error messages and check what's in the SQL error log.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JMSM SSCertifiable Points: 5440 More actions · Answer 2

Thanks a lot everybody

I've already the command and database stays in the same state.

I think that i should ask for the Backup Administrators to start the restore again.

RESTORE DATABASE <db name> WITH RECOVERY

Thanks and regards,

JMSM 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

GilaMonster (12/18/2009)
If that doesn't work, post the error messages and check what's in the SQL error log.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JMSM SSCertifiable Points: 5440 More actions · Answer 4

Hello again,

Thanks a lot but i've droped the database and backup admins will restart a new restore.

Before i've droped the database i run the following command to recover the tlog file.

CREATE DATABASE dbname

ON (FILENAME = 'E:\SQLData\dbfilename.mdf' )

FOR ATTACH_FORCE_REBUILD_LOG

The database open and i can see all objects that were not tables (views/stored procedure)

Thanks and regards,

JMSM 😉