Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Norway
    Posts
    7

    Exclamation Unanswered: Help! Databases are in "Loading" state after Restore from tape!

    I run MS SQL Server 2000 (8.00.194) on a Windows 2000 Server.

    After having problems with extremely large logfiles, I was told to stop the SQL Service, and delete the logs, and when I restarted the SQL Service the Server would recreate the log files. That did not happend, and the databases had "Suspect" state. After I've tried a few things found on this forum among others, I ended up restoring the db's from my last tape backup.

    This was successfully (with Veritas Backup Exec), but now the databases have "Loading" state. I've searched the forums, but haven't found a solution, please help!

    Thank you in advance!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In Query Analyzer, issue the following command:

    restore database yourdatabase with recovery

    Substitute your database's name with "yourdatabase".

    That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?

  3. #3
    Join Date
    Oct 2003
    Location
    Norway
    Posts
    7
    Originally posted by MCrowley
    In Query Analyzer, issue the following command:

    restore database yourdatabase with recovery

    Substitute your database's name with "yourdatabase".

    That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?
    Thanks for your answer. I tried it, and got this msg back:

    Server: Msg 4331, Level 16, State 1, Line 1
    The database cannot be recovered because the files have been restored to inconsistent points in time.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.



    Yes, I will not delete them again, but I seriously need to get these databases up and running.

  4. #4
    Join Date
    Oct 2003
    Location
    Norway
    Posts
    7

    Restore

    As you can see, I have full backup of the databases on tape.

    So If anyone could give me info on how to do a restore correctly, that will also solve my problem.

    Thanks, again.

  5. #5
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    here is a link to an article at Veritas that may help you out:

    http://seer.support.veritas.com/docs/258895.htm

    do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.

    It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.

    If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly
    drew
    ------------------
    http://wjtechnologies.com

  6. #6
    Join Date
    Oct 2003
    Location
    Norway
    Posts
    7
    Originally posted by drew
    here is a link to an article at Veritas that may help you out:

    http://seer.support.veritas.com/docs/258895.htm

    do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.

    It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.

    If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly
    Thanks for your answer, I'll look into what the article describes. I've understand that this backup isn't good enought, sp it will be changed as soon as I have my data up and running again. About the upgrade, it has not been done due to compatibility problems. Hopefully it will soon be patched with latest sp.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can try this code to force your database out of the loading state, but because it modifies system tables use it at your own risk:

    sp_configure 'allow updates', 1
    RECONFIGURE WITH OVERRIDE
    go
    update master..sysdatabases
    set status = status - 32
    where name = 'YourDatabaseName' and status & 32 > 0
    go
    sp_configure 'allow updates', 0
    RECONFIGURE WITH OVERRIDE
    go


    If this does not work, try dropping and recreating your databases prior to running MCrowley's RESTORE DATABASE suggestion.

    blindman

  8. #8
    Join Date
    Oct 2003
    Location
    Norway
    Posts
    7
    Seems like the veritas article was what I needed to at least get the first backup database back

    Thanks a lot everyone...one db to go, hopefully that's goes okay as well.

    This forum is awesome. Helping a n00b who started with sql a few hours ago, thanks again!

    - Everything is back and up and running from the backup.
    Good night ppl!
    Last edited by johnr; 10-27-03 at 15:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •