SQL Server Performance

Restore Issue

Discussion in 'General DBA Questions' started by fungbk, May 31, 2004.

  1. fungbk New Member

    Dear all

    One of my production database MS SQL 6.5 was suspect due to the harddisk failutre. Due to these databases are all in production. As a result, other good condition databases are still running for normal operations (i.e. add, change and delete). I plan to after office hour to restore the 'suspecet' databases again. Is there any consistent issues since both normal and 'suspect' databases are controlled by the same master databases ?

    Thanks in advance

    Kwan

    1 June 2004
  2. satya Moderator

    The master database controls user databases and the operation of SQL Server as a whole. It keeps track of user accounts, remote user accounts, remote servers that this server can interact with, ongoing processes, configurable environment variables, system error messages, the databases on SQL Server, the storage space allocated to each database, the tapes and disks available on the system, and the active locks.

    There wouldn't be any issues with consistency for other databases if one database is under suspect, and to reset the suspect mode you should SP_RESETSTATUS.

    The sp_resetstatus procedure can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure. SO be aware using this reset status there is a potential downtime for other databases too.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. fungbk New Member

    Dear

    Do you means that I need to restore suspect database from tape and then reset the database before it can work normal ?

    Kwan
    1 June 2004

  4. satya Moderator

    No, reset the status of the current SUSPECT user database.

    If you restore from tape then ensure to re-generate the load to update/insert transaction from application to this database since the last backup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. fungbk New Member

    Dear

    I was confusing, what is the purpose to reset the user database? Is it useful to reover the database the database was 'suspect' due the harddisk failure (i.e. bad sector). And, what is the meaning for 'Application', since I had just backup the suspect database. There should no lose if I restore the last backup from tape, is that right !

    Regards

    Kwan
    1 June 2004
  6. satya Moderator

    In your first post it was referred as one of the production database, where it assumed this was an user database.

    Ensure the current hardware situation is address and corrected, otherwise the restore will fail as one of the data devices are out of line. You must set the RESET STATUS of suspect database and then restore the from the good backup, this enables database back online.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. fungbk New Member

    Dear

    Please verifty and correct me about the restore steps

    1. Replace the damaged harddisk with a new harddisk
    2. Reset the database status
    3. restore the database from tape (archserve software - tree option)
    4. Restare the machine

    Any others ???

    Regards

    Kwan
    1 June 2004



  8. satya Moderator

    Absolutely fine with the steps.
    For information may also refer to the SQL books online for 6.5 version.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. fungbk New Member

    Dear

    One more question, since the old data file was corrupted. I cannot copy the old file to the new harddisk. Do I need this old data file to restore the backup, or I can simply restore the backup from the tape ?

    Thanks

    Kwan
    1 June 2004
  10. satya Moderator

    In that case, you must create the database with original size and specify the location for data and log files and then restore the backup from tape.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. fungbk New Member

    Dear

    I cannot find the command 'sp_resetstatus' in 6.5, any other command can be replaced. And, when I tried to restore the backup from tape. Error message like 'cannot login the agenet' message appeared. Any ideas ?


    Thanks

    Kwan
    2 June 2004
  12. Luis Martin Moderator

  13. fungbk New Member

    Dear

    I had already successfully restore the data files. However, the restored database status is alwasy in 'loading; mode but without do anything. Any ideas for this ?

    Many thanks

    Kwan
    2 June 2004
  14. Luis Martin Moderator

    Are you restoring from Work Station or Server?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  15. fungbk New Member

    Dear

    From server

    Regards

    Kwan
    02 June 2004
  16. satya Moderator

    Define the LOAD statement used to restore the database.
    Also confirm the version of SQL used, ver. 6.5 doesn't have this behaviour of keep database in LOADING state after restore.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. fungbk New Member

    Dear

    Actually the problem still exist. I tried to restore the database by using load command. However, the same case occured. And I had tried the emgerency mode. The result is so terriable. After I change the status to emgerency mode. I cannot even connect to others databases !!! What I need is to restore the old master files and save other healthy databases. (so lucky)

    Any other suggestion for such case?

    Regards

    Kwan
    5 May 2004
  18. derrickleggett New Member

    Do you actually have the .bak backup files, or do you just have the .mdf and .ldf files backed up? If you have the .bak files, restore them from tape onto disk. Then restore the databases from the .bak files using WITH RECOVERY option.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  19. fungbk New Member

    Dear

    Because I use the archserve for backup, so it can only be restored from it. I don't the exact format of the backup in the tape.

    Regards

    Kwan
    6 June 2004
  20. Chappy New Member

    ok to summarise a bit..<br /><br />you have a database marked as suspect.<br />you have a backup of the mdf and ldf files<br />but you cant restore over the top of this as it stands because the original database is suspect <br /><br />is this right ?<br /><br />Not sure if its possible, but try detaching the suspect database. Move the existing mdf and ldf files and replace them with those from the backup. <br />The reattach the database<br /><br />This will result in no downtime for your other databases<br />Maybe ive misunderstood tho <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />
  21. satya Moderator

  22. fungbk New Member

    Dear all

    I already solve the problem. I tried to restore the backup and copy the data file to another new setup SQL server. However, it stil in suspect mode. And I tried to turn to engerency mode. Thanks god, the suspect mode change to emgerency mode and data can be bcp out from server.

    Thanks again for all of you !!!

    Regards

    Kwan
    8 June 2004

Share This Page