Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Backups' best practices

I've been a SQL Server DBA for many years now.  I have never lost any data, so I must be doing something right :)  And lucky too :)

I'm in a new position, and of course, there are hearts and minds to win over.

We're using 2000.

The databases I formerly managed were all high-volume OLTP databases...lots of transactions...lots of activity.

The backbone of my plan was to do Full Backups at a proper interval, supplement those with Differentials, and do tranlog backups at frequent intervals.

I backed these up to disk (not the same disk as the SQL Server), then when the network guys backed up their servers at night, of course the .bak files would get backed up as well.

The system worked well, the users were happy because I could do a Restore easily using either EM or Query Analyzer (users' managers sometimes wanted to go back to a specific point in time, just before someone had made an error :)

I found that disregarding SQL Server's excellent backup tools (IMHO) and relying on tape only to be unnecessarily complicating things, plus I gave up a lot of flexibility.  Plus those tranlog backups are lifesavers in an OLTP system.

What do y'all think?  I would welcome comments.

Pardon the Houston accent :)

  • 3
1 Solution
You have an accent?  Seems normal here in Dallas...

Your plan is exactly what I would do in a heavy OLTP environment.

I would add however that you might want to look into SQL LItespeed to increaase both backup and recovery time.

I only have moderate exposure to the product, but I can tell you that I recently restored a 14GB database in under 4 minutes.
guillotjAuthor Commented:
Hey Kevin3NF, thanks for the response.  I'll look up Litespeed.  That 4 minutes restore time is phenomenal...

One other thing, you might getter better throughput backing up to local disks, then copying the file over to a different box.  Just a thought.

Thanks for the points :-)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now