locked
Disaster Recovery RRS feed

  • Question

  • Scenario:
    I have Databases which are backed up every night (Full Backup); the Backups are saved on a Tape then deleted from the Sever after three days. 
         1.The Backups are taken at 8pm the previous day and if something goes wrong, for example at 4ppm the next day I would have to Restore the last Backup then rerestorell the Log files since the last Backup (The Log Backups are done every 15 minutes), imagining the Database will need to be Back online as soon as possible; the procedure will take a very long time.

    I wanted to know if there is a better way of performing a Disaster Recovery other than the following Procedure:

    RESTORE DATABASE DB_TestLSN_Restore
    FROM DISK = 'c:\Backup\DB_TestLSN.bak'
    WITH MOVE 'DB_TestLSN' TO 'E:\MSSQL\DATADB_TestLSN_Restore.mdf',
    MOVE 'DB_TestLSN_log' TO 'F:\MSSQL\LOG\DB_TestLSN_log_Restore.ldf',
    NORECOVERY, NOUNLOAD, STATS = 10
    GO


    RESTORE LOG DB_TestLSN_Restore
    FROM  DISK = N'C:\Backup\DB_TestLSN_LOG1'
    WITH NORECOVERY,  NOUNLOAD,  STATS = 10
    GO


    RESTORE LOG DB_TestLSN_Restore
    FROM  DISK = N'C:\Backup\DB_TestLSN_LOG2'
    WITH RECOVERY,  NOUNLOAD,  STATS = 10
    GO

    And so on until the last log is Restored.

    2. Is there a way, if possible just to restore a single table if for example something gets deleted or if the table becomes corrupted?

    Thank you in advance!

    Friday, November 2, 2012 11:02 AM

Answers

All replies

  • to restore a single table if any data is deleted .

    restore the backup with a other name  use bcp and write the table content to a csv file

    and use bcp to insert the csv values intot he table

    to take the backup of a single table check my blog

    http://sqlservr.blog.com/2012/04/09/how-to-take-backup-of-a-single-table/


    Ramesh Babu Vavilla MCTS,MSBI

    Friday, November 2, 2012 11:22 AM
  • hi there,

    first of all you need to know that you can not restore or back up a particular table. the only way in teh current sql server is move that table to a filegroup and backup that file group. you can restore that file as well.

    second one with your existing backup strategy you have no other choice. you have to restore every single transaction log since your last full backup. how ever you can reduce this number by taking the differential backups.

    for example take a differential backup for every one hour. and take transactional backup for every 15 mins. in case of disaster you can easily restore the datbase.

    1) take current transaction log backup (for point of failure)

    2) restore last full backup

    3) restore recent differential backup

    4) restore every transaction log in order after your last differential back up with no recovery

    5) restore the last back up with recovery.

     

    i hope this helps you.  

    Friday, November 2, 2012 12:29 PM
  • As already mentiones, add in differential backups a number of times each day (say every 4:th hour), and restore mosre recent db backup, most recent diff backup and subsequent log backups.

    As for using filegroups for table backup: probably not usable for you. See this for why: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Shulei Chen Tuesday, November 13, 2012 9:08 AM
    Sunday, November 4, 2012 10:37 AM