Post reply

Differential Backup

  • August 4, 2003 at 5:29 pm

    #65317

    I ran a full db backup which took 6 hours starting at 1:00am and stopped at 7:00am. I then ran a differential backup at 12:00 noon.

    I wonder if someone can tell me what is the time covered by the differential backup. Does it cover all transactions since the START or the END of the last full backup? Thank you.

  • August 4, 2003 at 9:57 pm

    #468565

    Also, to apply the differential backup, do I need to restore the full backup in a special way? Thanks.

  • August 4, 2003 at 10:26 pm

    #468568

    I would assume it's from the END of the full backup, based on:

    a) LSNs in the headers of full and differential backups don't overlap.

    b) Full backups provide data integrity to the END time of the backup.

    c) I can't imagine a full backup clearing the DCM until it's finished backing up.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 5, 2003 at 7:06 am

    #468636

    Do you need to restore the full backup in any way?

    YES. You need to include the command

    WITH NORECOVERY

    in the FULL RESTORE and the command

    WITH RECOVERY

    in the DIFFERENTIAL RESTORE.

    See the BOL, use Index tab and enter Restore Database, select the entry for Transact_SQL.

    -SQLBill

  • August 5, 2003 at 10:17 am

    #468695

    Thank you, guys, for the helpful input. I already did a full restore of the database in the EM, which took 5 hours. Can I get around it without another full restore with the WITH NORECOVERY command?

  • August 5, 2003 at 12:06 pm

    #468710

    Nope.

    WITH RECOVERY is the default. Once the database is 'recovered' (WITH RECOVERY) no additional backups can be restored.

    You would have to start over again.

    -SQLBill

  • August 5, 2003 at 12:21 pm

    #468712

    Thank you for the quick response, Bill.

    Will the following three things do the same trick?

    1. Restore ... WITH NORECOVERY

    2. RESTORE ... STANDBY

    3. Enterprise Manager - Restore Database:

    Leave database read-only and able to

    restore additional transaction logs

  • August 5, 2003 at 2:55 pm

    #468733

    Yes they will. STANDBY has the advantage of the database being available for reading, but the disadvantage of preventing any transaction logs or differential backups being applied while anyone is connected to the database.

    Additionally, I suggest experimenting with striped backups (backups to 2 or more devices) to reduce backup times. I tend use use about 5 devices on large databases.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 5, 2003 at 3:57 pm

    #468739

    Thank you very much, Mark, for the infor.

    Backing up to multiple devices, will I be able to combine them into one data file and one log file during the restore?

  • August 5, 2003 at 4:07 pm

    #468742

    If you backup to 5 devices you must restore from those same 5 devices. The restore will restore the same number of database files (MDF, NDFs if any, and LDF/s) as the original.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 6, 2003 at 7:56 am

    #468828

    I had to restore a development database with full and differential backup.

    I tried to restore the database using TSQL command but got the error message "database in use need sys admin exclusive rights".

    So I tried to restore thru enterprise manager and was able to restore full backup.

    For differential backup I used the second recovery completion option "leave database nonoperational but able to restore additional transaction logs". I had to use this option because there is no option for differential backup restore and BOL refers to this option for both differential and transaction log restore.

    When I tried to restore database and it went into 'IN LOAD' mode. I got the error "cannot open default database <ID> Using master instead." I could not get back in to sql server and could not see databases.

    So I had to go thru NT domain account using advanced option "part of operating system".

    I did use restore with recovery and stopped showing 'IN LOAD' in enterprise manager but now whenever I restore full backup database

    it gives the message in the sql error log 'By passing recovery for database 'test'

    because it is marked IN LOAD'.

    How do I get rid of this? I tried everything but thru query analyzer and TSQL command it still finds database in use.

    I tried restoring thru query analyzer using other database from the drop down still it finds the database in use.

    Thank you in advance for your help.

  • August 6, 2003 at 8:28 pm

    #468917

    From master database in QA, you should be able to do something along the lines of:

     

    restore database [MyDB] from disk = 'TheFullBackup.bak' with norecovery

    restore database [MyDB] from disk = 'TheDiffBackup.bak'

    Cheers,

    - Mark


    Cheers,
    - Mark

Viewing 12 posts - 1 through 11 (of 11 total)

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