Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Okay, I've got my backups . . . Now What? 2

Status
Not open for further replies.
Gooser

Gooser

Technical User
Jun 28, 2006
803
US

The Current State of Affairs

I've got a backup plan and I execute it fairly regularly, but handling the files is burdensome and I'm looking for ideas.

I have my databases all scheduled to backup during our downtime (overnight.) I daily zip them up (for both the compression and the encryption), then pull them down to my desktop (where I store them on my internal hard drive, and copy them to an external hard drive as well.)

I have room to keep about 9-months' worth of backups on the server, which I do. So those are what I consider to be my live backups and can be restored if there are only data problems, or localized hardware problems.

I also have what I consider to be my on-network backups. I also have room on my desktop to store about 9-months' worth on my internal hard drive and 18-months' worth on my external hard drive. These backups would be most useful if we had a server failure or other problem that was limited to the server.

Monthly, I burn the month's backups from my desktop to a set of DVDs which then go in the safe. At the beginning of the month, the set in the safe (month before last) gets shipped to our other location, at the other end of the state, and replaced by the next month's (last month) set. So, I have backup DVDs going back to when I first started this job.

The backups are divisible by month and day, so if requested I could on the same day of a request, do a targeted restore down to any given day as far back as up to 8 weeks. And assuming I can get the set belonging to the month requested over-nighted to me, I can do a next-day restore of anything between 8 weeks and 18-months. There are no backups prior to that time, (but I wasn't here.) I think I have a pretty comprehensive backup strategy given our current infrastructure, but it takes too much time.

Too much time

The problem I have is that daily I have to spend quite a bit of time zipping up the backups and copying them down to my desktop. Is there some way I could automate this step so I could save myself a ton of time? What do you do in your shop? What do you see as best practices? What do you think of my strategy?

Any ideas or discussion on this will be greatly appreciated.



v/r

Gooser

Why do today
that which may not need to be done tomorrow [ponder] --me
Click to expand...

The Universal Model
 
Sort by date Sort by votes
Yes you can atomize the whole thing,
Add two more steps to your backup plan. You can put windows OS commands in SQL jobs.

So..
Step 1 backup db
Step 2 zip backup (Change the step type to Operating system)
Step 3 copy to share location. (Change the step type to Operating system and use xcopy or copy)

Also,
Do you have business need to keep so many copies of your backups? My business allows me to only keep 2 months worth of backups on Tape. You should also consider getting a copy off-site faster. What happens should your building burn down?

- Paul
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
This will be conditioned on teh version of sql server you are on but you can do it in 2K and '05 (05 is just easier and more intuitive)

Is there some way I could automate this step so I could save myself a ton of time?
Click to expand...

In your maintenance plan you can automate the compressing (zip) and you can automate the pull to your desktop but not sure I would use my desktop for such a critical step. anyhow you can test the connectivity to the desktop and if its up then zip...copy...done. you can do this with an ActiveX script or if on 05 use .NET to do it


What do you do in your shop? What do you see as best practices? What do you think of my strategy?
Click to expand...

You're strategy is good sense you have a copy stored in the safe and have such a long retention time. You should however check with the companies legal department to see if you should be keeping that long of a retention. The desktop part is bad. Typically you will have tape backups for large requirements of storage or SAN/NAS space allocated for just backups and then rotate them out and in based on the date of the backup. I don't keep anything older than a month. Remember this is a recovery point not archiving. A friend I know is going to usb drives for backing up. That idea is pretty good if you don't have the money for tape or large disk space like a SAN.

I didn't hear mention of log file backups. you should if you truly want to recover to a point in time.

Really money is the key. Whats the budget? A tape drive can run around $1000+ and then tapes are around $80 each I think at last seeing them. You can get 400GB tapes and use one tape for tons of backups if the DB is small. That meaning you can get away with only 10 tapes for 10 years ;-)

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote

There were a couple of things I neglected to mention.

Q: Do you have business need to keep so many copies of your backups?
A: Paranoia? Seriously, if I lose any level, I always have a fallback to a slightly older backup and the fallbacks are, I think, scaled appropriate to the risk.

Q: You should also consider getting a copy off-site faster. What happens should your building burn down?
A: I forgot to mention that the server is located in a server farm about 5 blocks away. So, if our building burns down, the backups on the server are good. If the building housing our server burns down, then I have backups as recent as our last day of business on my desktop.

Point: You should however check with the companies legal department to see if you should be keeping that long of a retention.?
Question: What are the legal ramifications of having old backups?

Point: I didn't hear mention of log file backups. you should if you truly want to recover to a point in time.
Counterpoint: I do log backups every-other hour during the workday, and once two-hours after the workday. Our business is such that traffic is minimal--almost non-existent--after-hours and I'm willing to sacrifice any potential for lost data outside of the normal workday in the interest of fewer backups.

Q: Whats the budget??
A: The budget is whatever it needs to be. If we need something, we can get it, we just have to justify it. I try to keep unneccessary expenditures to a minimum, though.

Our backups amount to about 7 DVDs per month (it varies from month to month, but June and July were both 7.) DVDs are cheap, lightweight, reasonably rugged, and the drives are ubiquitous. What's not to love?

Thank you guys so much for your input so far! I'd love to hear more.

--Gooser
 
Upvote 0 Downvote
>> DVDs are cheap, lightweight, reasonably rugged, and the drives are ubiquitous

Scratch and see if you're saying that still.

Bottom line is if you ahve a good budget then do what we suggested. Tape backups are the most stable if you ask me. Just remember you ahve to have the tape drive physically attached to the sql server to use a tape drive via sql backup otherwise you will need to backup the dbs to disk and then run the backups to tape (given the software you use, netbackup, backup ex etc....)

The legal issues are different for every caompany and depends on what it is your company produces or services provided. No one can help you with that. Only your legal department can.

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote

Scratch and see if you're saying that still.
Click to expand...

Get a magnet to close to your tapes and see how good they are--That's silliness. The DVDs go straight into a case as soon as they are burned, the only way they'll get scratched is if it is intentionally.

--Gooser
 
Upvote 0 Downvote
Gooser, If you truely have little restrictions on your budget then DVD backups are not better than tape.

I'm sure many will agree with me but if you don't then so be it.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote

Tape Sucks!

After reading this article, I cannot concur that tape is a reasonable data storage medium. Lots of people use it, but lots of people belive DDT is bad for you and the Beatles were the best band ever. Consensus does not equal correctness.

--Gooser
 
Upvote 0 Downvote
Dude, you read an article written by a company that sells disk storage ???? Are you serious?


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote

Yes. I have also had numerous problems restoring from tapes on various occasions in various environments using tapes and drives from various manufacturers. So, sorry for citing a source, even if they are biased.

That doesn't negate the fact that tapes are an unreliable storage media.

--Gooser
 
Upvote 0 Downvote
If you can't afford the Beatles, I would back up to tape as well...

Someone was telling me over the weekend about an article they read saying that after about a year, bad things start to happen with burned DVD's. I'll see if I can track it down.



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Upvote 0 Downvote
Someone was telling me over the weekend about an article they read saying that after about a year, bad things start to happen with burned DVD's. I'll see if I can track it down.
Click to expand...

Do I know that guy?

This one is about cd's but DVD's are actually worse. Depending on the brand. And it's not always the expensive ones that are the best.


And that doesn't even account for scratching. Or badly written/not verified. If you use your DVD's for movies they will actually last longer not because they don't loose data but because most programs compensate/skip the loss of data untill you start to see it. For zipped data you really don't want that, since loosing a few byte will mean that the file becomes unreadable.

And if you do buy DVD's then buy several spindles and use a DVD from a different spindle every day. And different brands. On some brands you could see the writting on the front reflected in the bad parts.

Christiaan Baes
Belgium

"My old site" - Me
 
Upvote 0 Downvote
>>>Do I know that guy?

You just might ;-)

Star for you, that is the exact article I was thinking of

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
>>tapes are an unreliable storage media.

That's just wrong. The setup and operations was the probelm if that is your experience.

poumd for pound there is no better solution either. Disk is cheap yeah, but the ratio to tape/disk just doesn't lead me to by disk over a good tape. With the the advent of LTO3 and the TB tape disk cost even for maintenance is horrid. Let's see you cost out a SAN with 40TB verses a veritas tape library with 40TB in it and show your saving. Exspecially when you have to send them off-site (like you should) and ned to worry about someone dropping the case of drives verses tape.

We once had a whole case of tape fall out of the back of a truck onto the interstate where I used to work. Every tape was fine after the drop and bounce through the Chicago traffic. Wonder how disk would have handled that. To be honest DVD technology doesn't even belong in this topic. It's a backup strategy you should leave at home backing up your My Pictures directory


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote
Hi,
i'm trying to do what Paul outlined - the 3 steps. However, i can't get the second step to work. i don't know what the OS commands to zip a file is. can anyone help me out please?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JoPaBC
  • Locked
  • Question
Schedule and automate backups of SQL Express databases in SQL Server Express 1
Replies
5
Views
310
JoPaBC
JoPaBC
tekpr00
  • Locked
  • Question
Backups in a multiserver environment while using Central Management Server and Server Groups
Replies
0
Views
99
tekpr00
tekpr00
wbodger
  • Locked
  • Question
SQL Server 2008r2 backups
Replies
4
Views
87
wbodger
wbodger
wbodger
  • Locked
  • Question
Backups with Ola Hallengren Scripts
Replies
0
Views
83
wbodger
wbodger
niall5098
  • Locked
  • Question
sql server always on backups
Replies
0
Views
37
niall5098
niall5098

Part and Inventory Search

Sponsor

Back
Top