Post reply

When to take backup?

  • March 27, 2007 at 12:15 am

    #172600

    what are situations which will tell me to take backup of SQl Server. i.e in which conditions i should take backup so that the DB's or data will be backed up if something goes wrong?

    so in which scenarios i should backup SQL server before it goes in inconsistent state.

    i will be using some other S/W for backuping up that SQL Server.

    Thanks

    -Abhishek

  • March 27, 2007 at 8:32 am

    #697160

    Hi Abhishek,

     

    It depends on how many data you can afford to lose.

     

    If you can afford losing a full day of data, go with your database in Single recovery mode and do a full backup once a day.

     

    But if you can’t lose much or any data, consider this scenario.

     

    Database in Full recovery mode.

     

    This is the strongest scenario, Do a full backup once a day at 12AM and hourly or event at each 15min if you want, do a transaction log backup. If you want to save some space you can do once a week a Full backup and a differential backup rest of the week.

     

    Don’t forget that the recovery sequence is:

     

    1)      Backup the tail end of the log. That will be use to recovering to the second before the crash.

    2)      Restore your last Full backup

    3)      Restore your last Diff backup. (If applicable)

    4)      Restore, from oldest to latest, transaction log backup.

    5)      Restore the tail end of the log.

     

    Don’t forget to leave your database in the recovery state until the last recovery.

     

    This is the idea, email me if you need mode details.

     

    Hope this help you.

    Best Regards,

    David, MCAD.NET

  • March 27, 2007 at 8:47 am

    #697170

    Thanks a lot David...

    ya above info is really useful....

    but i am mostly interested in when to take backup? means what are conditions which will tell me like "take backup coz SQL Server is going down or may be in inconsistence state(before going in Inconsistence State)" so that i can immediately take a backup before i loose data.

    Thanks in Advance...

    -Abhishek.

  • March 27, 2007 at 8:57 am

    #697177

    Well, if you do regular backups and maintenance, using maintenance plan, rebuid index, check data integrity, ect... You should be good.

    With this backup strategy i think you're covered!

    SQL server is very stable. Don't worry

    Best Regards,

    David, MCAD.NET

  • March 28, 2007 at 1:30 am

    #697328

    backup is a preventive measure, so u will have to backup first and then use the backup in case of failure. as u can expect, sql server will not give u any warning before moving to inconsistence state or before loosing data...

  • March 28, 2007 at 7:34 am

    #697385

    I agree, take regular bakups and you'll be in good shape!

    Best Regards,

    David, MCAD.NET

  • April 2, 2007 at 9:34 am

    #698123

    Most likely by the time you think the data is going to be inconsistent it will be too late to do a backup that will do you any good (there are of course exceptions to this).

  • April 2, 2007 at 2:52 pm

    #698179

    Another good time to take a backup is before you do major changes to any schema in the database.  The question to ask yourself is do you want to be able to recover quickly from this change.

    Marvin Dillard
    Senior Consultant
    Claraview Inc

Viewing 8 posts - 1 through 7 (of 7 total)

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