Post reply

Is This a good plan

  • March 12, 2004 at 10:49 am

    #178714

    First thanks to all who will take the time to reply to my thread.

    I am confused about a few things on SQL Server regarding backup and restoration plans. I am new to SQL server and can only really work with Enterprise Manger. I am unsure of .bak, .ldf, .trn files. The .bak is a full backup which contains all of your data up to the backup. When you run a full backup does it automatically update your transaction log? .trn's are all your data changes from the point of a differential. Differentials are data that has changed since your last full backup. Correct? But what is the log .ldf file, and how do you back that up with Enterprise manager

    My plan is as follows;

    hourly differential on database from 5 a.m. to 7:00 p.m. overwrite each hour.

    every fifteen minutes a transaction log backup, delete if older than 1 hour

    nightly full backup at 7:15 p.m.

    Is this a good plan? what about my logs when should I back those up, should those go to tape backup? and how do I do schedule a job for them with Enterprise Manager?

    I read in my book the following for restoring data;

    first: most recent full database backup

    Second: most recent differential backup

    third transaction log backups sequestially from the one just ater most recent differential backup until most recent log backup

    finally: active transaction log backup (if it was able to be backed up)

    The item in red until most recent log backup confuses me, how and where do I back this up and how with Enterprise Manager do I do this.

    If examples with syntax are given, please be gentle and give examples with the sample database below as that is one of my db's.

    Sample database: BCT

    Sample Backups: BCT_FULL.BAK, BCT_DIFF.BAK, BCT_TRANS.TRN

    I apologize for the length of this thread, but I am uncertain of what I should do.

    DLR 

  • March 12, 2004 at 1:50 pm

    #498603

    What version of SQL Server are you using?

    For SQL Server 2000, the backup files for FULL, Differential, and Log files are all .bak. At least mine are.

    If your database is set for FULL recovery mode, then you can do LOG backups. If it is set for SIMPLE (and some system databases can ONLY be set for this), you can not back up the LOG.

    What's the log? That's the transaction log. It keeps track of everything that happens on your databases.

    Why should I back up the log? The log enables you to do a 'point-in-time' restore. Let's say someone deleted information at 1pm. You don't know what they deleted. You could backup the log. Restore the full backup from prior to 1 pm, then restore all the transaction log backups since then, but on the last one you would use WITH STOPAT 1255pm or something like that. Then the delete never happened.

    Now onto your main question: is your strategy good? Only you can answer that.

    Ask yourself and your managers: How important is your data? How much data can you afford to lose? Then base your backup strategy on the answers to those questions.

    If you can afford to lose 24 hours of data, you only need to do a full backup once a day and nothing else. If you can't afford to lose any data, you need to do backups more often.

    I do a full backup once a day, a differential 12 hours after the full backup, and transaction log backups every hour (except when the full and differential are being done). My data is critical and I can't afford to lose one hour of it.

    -SQLBill

  • March 12, 2004 at 2:52 pm

    #498635

    Thanks SQL Bill, I think that sums it up. do you by chance know if you can crete a Database Maintenance Plan that can do a differential?

    DLR

  • March 15, 2004 at 8:20 am

    #498776

    I don't know, I have never used the database maintenance plan wizard. I do all my own BACKUP commands and turn them into jobs run on my schedule.

    -SQLBill

Viewing 4 posts - 1 through 3 (of 3 total)

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