Backup Plan

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
shankshankAuthor Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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).
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shankshankAuthor Commented:
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?
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.