Backup Plan

Posted on 2006-04-13
Last Modified: 2010-04-03
I am trying to figure out a IT company policy for backups, and here is what I have come up with.

We are using Veritas Backup Exec 10d, on a standalone server with a terabyte of space and lt0 tape drive.

We have daily (mon-thur) differential tapes set 1 and 2. Set 1 runs odd months and set 2 runs even months. After a month, the set is taken off site and stored, until next month when it returns. Upon arrival, it is fully formatted and used again.

On Fridays, I run a full backup. The last friday backup is archived as a monthly.

Month 1
M  T   W  T   F
D1 D1 D1 D1 W1
D1 D1 D1 D1 W2
D1 D1 D1 D1 W3
D1 D1 D1 D1 W4

Month 2
M  T   W  T   F
D2 D2 D2 D2 W1
D2 D2 D2 D2 W2
D2 D2 D2 D2 W3
D2 D2 D2 D2 W4

So around 9PM, we have a script which dumps our SQL databases from servers to a local folder on that particular server. From there was thinking to copy that SQL dump file to a folder on the backupserver, which the backup jobs incorporate.

When backups run, they run to the backup server, and then a duplicate backup job runs immediately to copy it to tape.

This is pretty much what I have thought about so far, any help on changes, more efficient use, etc would be great. The confusion comes when  trying to manually delete the SQL dumps on the backupserver folder, so that every days don't get backed up to every newer tape.
Question by:shankshank
    LVL 2

    Expert Comment

    That is a nice backup routine, it all depends on how long your copany wants to retain data for. The norm is 3 months.

    What i do is the following:

    Mon - Thu - Week 1-4 giving me a full month of possible restores.
    Fri - Weekly 1-12 giving 3 months of possible restores.
    Last working day of each month - Monthly 1-12 thus giving a possible of a full year of backups.

    Then on last day of each year you do a yearly tape for historic reasons. This tape is never overwritten.

    Hope that helps
    LVL 9

    Expert Comment

    If you setup SQL to do its backup to a UNC path on the backup server you won't have the problem of deleting the backup files after a day or two since the maintenance plan backup can be set to delete the files for you.;en-us;555128 tells how to setup SQL to backup to UNC.
    LVL 5

    Author Comment

    I was thinking about doing that, but then I realize if i backed up to the local drive, I would have some form of redundancy, and allowing me to restore with going to tape, unless the servre completely fails.
    LVL 4

    Expert Comment

    The plan looks good, but I recommend a regular schedule of checking the backups.  Many times the backup runs for years, and when you need it you discover that all the tapes are blank because something went wrong and they were never tested.  So get someone to restore a file from backup every month or so (hopefully someone different than the person who created them).
    LVL 9

    Accepted Solution

    If you want the SQL backup files on both machines then since they are named by date and there isn't any way to substitute the date in the filename in a batch prog you'll have to manipulate directories instead:

    del c:\sqlbackup\folder3\*.*
    copy c:\sqlbackup\folder2\*.* c:\sqlbackup\folder3
    del c:\sqlbackup\folder2\*.*
    copy c:\sqlbackup\folder1 c:\sqlbackup\folder2
    del c:\sqlbackup\folder1\*.*
    copy \\SQL\backup\*.* c:\sqlbackup\folder1

    That would put today's SQL backup in folder1 on the backup server, yesterday's would be in folder2 and the day before;s in folder3. Anything older than that gets deleted automatically.

    As regards the tape rotation it seems fine although there are sometimes 5 fridays in a month. I presume you're going to manually move the last Friday tape into a monthly media set so it takes on that set's overwrite/append properties rather than the weekly properties. You can specify a special backup to run on the last Friday but I son't know a way of easily excluding the last Friday from ther normal backup so manually moving from one set to the other is probably easiest.
    LVL 5

    Author Comment

    samb39 as for testing, I run a verify after the backup to tape is complete, but you are saying to actaully try and restore a file?
    LVL 4

    Assisted Solution

    Yes.  I recommend actually restoring a file on some regular basis.  

    I have seen backups that were worthless because they were failing, and the person replacing the tapes did not read the error message, and I have also seen backups that failed because the person replacing the tapes did not correctly label them and mixed them up, overwriting tapes with later backups.

    I don't know any way to be really sure it is working except to really restore a file.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A bootable USB key can be very handy now-a-days. My favorite USB key consists of our Windows 7 image, network card drivers (to connect up to a Ghost server), the latest BIOS updates for all of our PCs and CopyWipe (to erase a retired PC) Creating…
    How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
    This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now