Post reply

restoring a backup

  • December 12, 2006 at 6:21 am

    #193440

    Hi,

    I have taken a full backup on 11/1/2006 and the file name is dbback1 and after that I have been taking differential backups everyday at 1pm and 8 pm.

    But on 11/21/2006 I took a full backup dbback2. but on 11/28/2006 I have deleted some of the data in a table tbl1. but I need to restore the data. So i tried to restore my database.

    As I took the fullbackup dbdback2 on 11/21/2006 My backup file dbback1 is invalid.

    however I took another full backup on 11/29/2006 as dbdback3.

    So is there anyway I can restore the data I have deleted on 11/28/2006.

     

    Thanks.

  • December 12, 2006 at 7:36 am

    #677466

    So are you saying you have tried to restore the full backup from 11/21 and the differentials prior to your delete on 11/28 and the data is not there?

    Mark

  • December 12, 2006 at 7:48 am

    #677472

    Have you continued to do the diff backups every day at 1 and 8pm?

    If you have, then you should be able to restore your full backup from 20061121, and after that the diff that is closest, but not after, when you deleted data from tbl1 at 20061128.

    If you have used the same file - dbback1 - also for all diff backups, then you need to specify the correct diff backup from those that are contained in the file, you can't use the first one since it probably will be the wrong time.

    You can use RESTORE HEADERONLY to see what's in your dbback files.

    /Kenneth

  • December 12, 2006 at 7:58 am

    #677478

    I am restoring the full backupbackup that was taken on 11/21/2006 and then trying to apply the differential backup of 11/1/2006.

     

    I am getting the following message.

    Cannot apply the backup on device E://....dbback1 to database DB1. Restore database is terminated abnormally.

    Thanks.

  • December 12, 2006 at 8:12 am

    #677485

    You're trying to apply a differential backup that was taken prior to the full backup you've restored.  You can't do that, they're out of sequence. 

    HTH

    Mark

  • December 12, 2006 at 8:15 am

    #677486

    No, I am trying to do the following.

     

    First restoring the fullback taken on 11/21/2006 and then trying to apply the differential backups of the backup taken on 11/1/2006 starting from 11/21/2006 in stead of 11/1/2006.

     

  • December 12, 2006 at 8:26 am

    #677493

    That's what I'm hoping to get across to you.  You can't apply any differential backup that was taken prior to the full backup you've restored from 11/21.  As long as you have differentials after you're 11/21 full backup, you can recover your data prior to your 11/28 delete.

    Order of operations:

    Restore your full backup from 11/21

    Restore only differentials taken AFTER the full backup from 11/21.

    If I'm not understanding, then I apologize.  But from what you are describing, you can't do what you are trying to do.

    Mark

  • December 12, 2006 at 10:50 am

    #677547

    When you did a full backup on the 21st, that 'broke' the differential chain and restarted it. Any diffs you made before the full backup on 11/21 belong to and only to the backup on 11/1. Any differentials you made after the full on 11/21 belong to the 11/21 backup chain - until the 11/29 when you did ANOTHER full backup.

    One problem, you aren't doing any transaction log backups, so you cannot restore to a 'point-in-time', it's all or nothing.

    You deleted something on 11/28 (what time?). You need to restore the full backup from 11/21 and any differentials made AFTER that and BEFORE the delete. You probably will lose some data. Let's say your last diff was 11/28 at noon and your delete happened at 1 PM. You will lose 1 hour of data (there's no backup to cover noon to 1 PM). The next diff backup includes the delete.

    -SQLBill

  • December 12, 2006 at 11:30 am

    #677569

    backupdiskfile:penMedia:Backup device d:\MSSQL\BACKUP\database1_00_08

    Actually I am trying to restore the database backup from folder E.But the the restoration process is trying to restore backupdiskfile from drive D and its giving the error the file could not be found.

    So how can I restore the dabase from only the backups from the folder E.

    Thanks.

  • December 12, 2006 at 3:00 pm

    #677643

    You will need to script the backup and specify in the SQL where to find the backup file you are using to restore.  Also, You only need to restore from the latest differential file prior to when the data was dleted.  Each differential backup contains all database changes since the last full backup.  Be sure to use the NORECOVERY option when restoring the Full Backup.

     

  • December 13, 2006 at 8:51 am

    #677804

    HEre is what I am doing.

     

    So far I tried to restore the database from enterprise manager.

    Now started restoring it from query analyzer.

    The first step is restoring the fulllback taken on 11/21/2006 with NORECOVERY  option. I am restoring the database db1 as restore_test_db.

    RESTORE DATABASE restore_test_db FROM DISK = 'E:\MSSQL\BACKUP\db1_backup_11212006'

    WITH MOVE 'db1_Data' TO 'D:\MSSQL\Data\restore_test_db_data.MDF',

    MOVE 'db1_Log' TO 'D:\MSSQL\Data\restore_test_db_Log.MDF',

    NORECOVERY

     

    After that how can i restore my differential backup.

     

    MY differential backup was taken onto the following location.

    E:\MSSQL\BACKUP\db1_backup_11012006

    and it has differential backups starting from 11012006  until 12032006.but I need to restore the differenital backups from 11212006 to 11282006( the day I have deleted the data) to the database restore_test_db.

     

    Thanks to all of you guys for all the help.

     

  • December 13, 2006 at 2:17 pm

    #677912

    Question: On the 28th, what happened first?

    Did the Diff backup happen and then the delete? Or was it the delete then the backup?

    If the delete happened before the backup, then you can NOT use the 11/28 differential.

    What is the name of the differential backup files? This (db1_backup_11012006) can't be it as that is the name of the full backup.

    -SQLBill

  • December 13, 2006 at 2:20 pm

    #677914

    BTW - you just use the same command as for the full backup. But change it to WITH RECOVERY.

    The only difference is you have to 'point' it to the differential backup instead of the full backup.

    Update: Also, remember as said before, you only need to restore ONE differential. The most recent one that does NOT have the delete.

    -SQLBill

  • December 13, 2006 at 3:11 pm

    #677929

    First, you need to know the file position of your 11282006 differential backup from your 'E:\MSSQL\BACKUP\db1_backup_11012006' backup file. Use

    RESTORE HEADERONLY from disk = 'E:\MSSQL\BACKUP\db1_backup_11012006'

    Take nore of the POSITION number of your differential backup for 11282006.

    Restore your FULL backup as you did, then restore your differential backup as follows.

    RESTORE DATABASE restore_test_db FROM DISK = 'E:\MSSQL\BACKUP\db1_backup_11012006'

    WITH MOVE 'db1_Data' TO 'D:\MSSQL\Data\restore_test_db_data.MDF',

    MOVE 'db1_Log' TO 'D:\MSSQL\Data\restore_test_db_Log.MDF',

    RECOVERY, FILE = 28 -- use the position number of your 1128 diff backup here

     

    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

Viewing 14 posts - 1 through 13 (of 13 total)

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