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

  • 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

  • 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
  • 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 😉

  • 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
  • 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 😉

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

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