[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Backup Plan

Posted on 2006-04-13
Medium Priority
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
  • 2
  • 2
  • 2
  • +1

Expert Comment

ID: 16446700
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

Expert Comment

ID: 16452142
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. http://support.microsoft.com/default.aspx?scid=kb;en-us;555128 tells how to setup SQL to backup to UNC.

Author Comment

ID: 16452888
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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Expert Comment

ID: 16458270
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).

Accepted Solution

David_Fong earned 750 total points
ID: 16459329
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.

Author Comment

ID: 16468823
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?

Assisted Solution

samb39 earned 750 total points
ID: 16470673
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

873 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