Post reply

Database in Restoring State since upgrade of OS to Windows Server 2008 Enterprise

  • January 17, 2014 at 3:27 pm

    #288439

    I do not know why but a Database in Restoring State since upgrade of OS to Windows Server 2008 Enterprise?

    Any ideas?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 17, 2014 at 3:54 pm

    #1681295

    Restoring or Recovery? Just want to be sure.

    Can you provide more information regarding the database? Is it the target of log shipping or mirroring?

  • January 17, 2014 at 4:10 pm

    #1681300

    Lynn Pettis (1/17/2014)

    Restoring or Recovery? Just want to be sure.

    Can you provide more information regarding the database? Is it the target of log shipping or mirroring?

    It went into a restoring state.

    No log shipping or mirroring.

    I restored the database with a backup that I performed just before the upgrade.

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 17, 2014 at 4:27 pm

    #1681302

    I restored the database and it was operational.

    Then it went back into a restoring state.

    So I dropped it and started a restore.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 17, 2014 at 5:30 pm

    #1681312

    Could you post the full restore command you used?

  • January 18, 2014 at 7:12 am

    #1681333

    Lynn,

    I had to drop the database and restore. Basic syntax.

    I think the problem was that they did not stop the SQL Server Services before they started the Windows upgrade.

    I would bet the farm.

    Thanks!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 18, 2014 at 7:37 am

    #1681335

    Welsh Corgi (1/18/2014)

    Lynn,

    I had to drop the database and restore. Basic syntax.

    I think the problem was that they did not stop the SQL Server Services before they started the Windows upgrade.

    I would bet the farm.

    Thanks!

    Okay, I understand you really don't want me to help so I will go away.

    I just don't understand why you refuse to post the complete RESTORE DATABASE command you used.

    If your database is still in RECOVERY mode try this:

    RESTORE DATABASE [databasename] WITH RECOVERY;

  • January 18, 2014 at 8:52 am

    #1681338

    Lynn Pettis (1/18/2014)

    Welsh Corgi (1/18/2014)

    Lynn,

    I had to drop the database and restore. Basic syntax.

    I think the problem was that they did not stop the SQL Server Services before they started the Windows upgrade.

    I would bet the farm.

    Thanks!

    Okay, I understand you really don't want me to help so I will go away.

    I just don't understand why you refuse to post the complete RESTORE DATABASE command you used.

    If your database is still in RECOVERY mode try this:

    RESTORE DATABASE [databasename] WITH RECOVERY;

    You are being silly.

    The database has been restored

    The restore is not the problem.

    RESTORE DATABASE DW

    FROM DISK = 'D:\Backup\Complete\DW\DW_backup_2014_01_17_153721_7634016.bak'

    WITH REPLACE,

    RECOVERY,

    MOVE 'DW' TO 'D:\MSSQL\Data\DW.mdf',

    MOVE 'DW_Horizon' TO 'D:\MSSQL\Data\DW_HorizonObjects1.NDF',

    MOVE 'DW_log' TO 'D:\MSSQL\log\DW.ldf'

    The restore worked fine.

    The database went into a restoring state because the SQL Server Service was not stopped during the OS Upgrade.

    Do not assume that I do not want help.

    Thank you.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 18, 2014 at 8:54 am

    #1681339

    The RESTORE Database WITH RECOVERY by itself did not work.

    I had to drop the database and restore WITH RECOVERY.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 18, 2014 at 10:01 am

    #1681348

    Welsh Corgi (1/18/2014)

    Lynn Pettis (1/18/2014)

    Welsh Corgi (1/18/2014)

    Lynn,

    I had to drop the database and restore. Basic syntax.

    I think the problem was that they did not stop the SQL Server Services before they started the Windows upgrade.

    I would bet the farm.

    Thanks!

    Okay, I understand you really don't want me to help so I will go away.

    I just don't understand why you refuse to post the complete RESTORE DATABASE command you used.

    If your database is still in RECOVERY mode try this:

    RESTORE DATABASE [databasename] WITH RECOVERY;

    You are being silly.

    The database has been restored

    The restore is not the problem.

    RESTORE DATABASE DW

    FROM DISK = 'D:\Backup\Complete\DW\DW_backup_2014_01_17_153721_7634016.bak'

    WITH REPLACE,

    RECOVERY,

    MOVE 'DW' TO 'D:\MSSQL\Data\DW.mdf',

    MOVE 'DW_Horizon' TO 'D:\MSSQL\Data\DW_HorizonObjects1.NDF',

    MOVE 'DW_log' TO 'D:\MSSQL\log\DW.ldf'

    The restore worked fine.

    The database went into a restoring state because the SQL Server Service was not stopped during the OS Upgrade.

    Do not assume that I do not want help.

    Thank you.

    Really? I am being silly? You said your database went back into RESTORING. I asked to see the command you used for the restore and you refused to post it when I first asked.

    At this point, I think your database was in RECOVERY mode due to the OS upgrade and reboot.

    If you are going to ask for help perhaps you should be more willing to answer peoples questions fully and completely, you may get better responses in return.

  • January 18, 2014 at 4:57 pm

    #1681363

    U have to be kidding.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 19, 2014 at 2:46 am

    #1681378

    The most common reason for a DB to restore and remain in the restoring state is the person running the restore ran RESTORE DATABASE ... FROM DISK ... WITH NORECOVERY, hence why the first thing to do when someone has that problem is ask exactly what restore statement they ran.

    Other reasons would be an interupted restore, which from your description it sounds like it was, the restore was interpreted and the restore has to be started over. This is the only time when RESTORE DATABASE <database name> WITH RECOVERY won't recover the database

    Finally a rare reason is someone, after the restore, runs BACKUP LOG ... WITH NORECOVERY, either manually or in a job.

    The windows update wouldn't leave the DB in the restoring state, it might, if it messes with files or file permissions, leave the DB in a RECOVERING or RECOVERY_PENDING state

    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

  • January 21, 2014 at 1:30 am

    #1681758

    A backup was performed by Microsoft thru the GUI and unchecked the Backup the Tail of the Log and leave the Database in a restoring state.

    I was tired. I do not understand why this was necessary.

    :doze:

    Thank you! 🙂

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 21, 2014 at 1:38 am

    #1681763

    In other words, this:

    GilaMonster (1/19/2014)

    Finally a rare reason is someone, after the restore, runs BACKUP LOG ... WITH NORECOVERY, either manually or in a job.

    When you check the 'backup the tail of the log' in the maintenance plan, it means that when the job runs, the DB will switch into RESTORING at the end of the backup. So whoever set the maintenance plan up either didn't realise what the option did or selected it accidentally.

    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

  • January 21, 2014 at 1:46 am

    #1681765

    GilaMonster (1/21/2014)

    In other words, this:

    GilaMonster (1/19/2014)

    Finally a rare reason is someone, after the restore, runs BACKUP LOG ... WITH NORECOVERY, either manually or in a job.

    When you check the 'backup the tail of the log' in the maintenance plan, it means that when the job runs, the DB will switch into RESTORING at the end of the backup. So whoever set the maintenance plan up either didn't realise what the option did or selected it accidentally.

    So I should turn it off on all backup maintenance plans?

    The Database would be ok for a while but go into a restoring state.

    I have used the backups on a routine basis to refresh DEV and other environments but I never experienced this problem before.

    Thank you Gail. 🙂

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 18 total)

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