backup & restore confusion

  • Hi,

    I'm trying to familarize myself with backup and restore on SQL server 2000. I was wondering if you guys could go through my process here and make corrections as necessary?

    The database is set to FULL recovery mode, and I have a maintenance plan in place which does a full backup at about 2am every morning.

    Added to that, I do transaction log backups every 2 hours throughout the day via another maintenance job.

    From what I've read, to restore the database onto another server (I'm assuming my server is toast and I NEED to move to a new one, but my backups are safe and sound), I would create a database with the same name, restore the full backup, then restore all the transaction logs that have been created since the full backup was created.

    So if I did the backup at 2am, and then at 11am my database (for now, lets call it TestDB) crashed and burned, I could go to a new server (lets assume I have one setup and ready to go), create the database (a new database with the name TestDB)

    I would then restore my full backup (which was created at 2am)

    RESTORE DATABASE TestDB FROM 'E:\TestDB.BAK' WITH NORECOVERY

    I would also then restore my transaction log's in order

    RESTORE LOG TestDB FROM 'TestDB_tlog_200805020210.TRN' WITH NORECOVERY

    RESTORE LOG TestDB FROM 'TestDB_tlog_200805020410.TRN' WITH NORECOVERY

    RESTORE LOG TestDB FROM 'TestDB_tlog_200805020610.TRN' WITH NORECOVERY

    RESTORE LOG TestDB FROM 'TestDB_tlog_200805020810.TRN' WITH NORECOVERY

    RESTORE LOG TestDB FROM 'TestDB_tlog_200805021010.TRN'

    (by leaving the WITH NORECOVERY off, it should default to WITH RECOVERY, correct?)

    This should now bring the database back to the point it was at (with all committed transactions) up to 10:10am when the last transaction log was made?

    My confusion arises because everything I've read says I need to do a differential backup as well, but I cant find the differential backup option in the maintenance plan. I know I can create a scheduled job for it.. but my assumption was I would only do a differential backup to reduce the number of transaction logs I need to restore. If its so important to do the differential backup.. why isn't it in the maintenance plan??

    Hope you guys can clear some of this up for me.. I really appreciate it.

    Thanks!

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • I'm new, so I can only say that I *think* your sequence and understanding is correct, and what you laid out will accomplish what you seek.

    But I do know that you don't have to first create the empty database on the new server - a restore will create the database for you.

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • thanks ahutchens.. i'll look into that.

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • just to add:

    It's not mandatory to have diff backups.

    Differential backups can help out if you have a huge log-backup load.

    In that case,by using diff backup, you can skip restoring log backups up to the point where the diff backup is taken.

    Read BOL for more info on diff backups. (a diff backup always goes back to the previous full backup, so is incremental by nature)

    You should test a recovery cycle to find out if you will benifit on using diff backups. To create a diff backup, sqlserver needs to read the full db ! so that may be some IO overhead.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • thanks alzdba.. that certainly helps.. i'll do a little testing

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • I recommend doing transaction log backups much more often. I usually set them up to run at least every 15 minutes, 24x7.

    This will reduce the amount of data loss in the event of a problem, and will help to prevent unreasonable growth in the size of the database transaction log file.

  • Thanks for your thoughts Michael.. I was planning on increasing the frequency at some point.. i just took over these databases a week ago.. they had a single transaction log backup being performed a day.. 5 mins after the backup of the database.. mostly pointless apart from getting the log file back down to size..

    I've got them set to every 2 hours for a couple of reasons

    1) they're not business critical databases.. if we lost a days worth of data, it'd hurt, but it wont kill us

    2) i just want to make sure everything runs smooth with every 2 hours

    once i'm sure it all runs smoothly on 2 hours, i'll drop it to 1.. then a half.. then every 15 minutes..

    i hadn't really considered the log file getting large, more data loss, but its an excellent point..

    Thanks! 🙂

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • There is no point in not running them every 15 minutes right now. You are just running a bigger job every two hours, instead of a small job every 15 minutes.

    You are more likely to have performance problems from running them less often if it forces the transaction log file to grow in size when the file is full.

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

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