Backups' best practices

Posted on 2005-03-23
Medium Priority
Last Modified: 2006-11-17
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 :)

Question by:guillotj
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 21

Accepted Solution

Kevin3NF earned 200 total points
ID: 13613147
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.

Author Comment

ID: 13613421
Hey Kevin3NF, thanks for the response.  I'll look up Litespeed.  That 4 minutes restore time is phenomenal...

LVL 21

Expert Comment

ID: 13613429
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 :-)
LVL 21

Expert Comment

ID: 13613523

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

801 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