May Question: What’s Your Backup Strategy?
Question of the Month | May 1, 2011 | 2:00 pmAs DBAs, one of our biggest responsibilities is to protect our data with backups. So, for this month’s question, “What’s your backup strategy?”, please share with us how your approach your backup process. For example, do you take full backups daily and trasaction log backups hourly? Or do you perform full backups weekly, differential backups daily, and transaction log backups every 30 minutes. Tell us what you do, and how you decided on the process you have. Post your responses to the SQL Server Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.
Because there is no right or wrong answer, this month’s winner will be selected randomly from all the entries that are received.
|
|
The prizes this month are an Amazon.com voucher worth US$50.00 and a license for SQL Backup Pro 6.4 with one year of support and upgrades. SQL Backup Pro compresses, strengthens and encrypts SQL Server backups. To find out more, visit the Red Gate website. |
- Questions About SQL Server Backups
- How Often Do You Backup Your Transaction Logs?
- Winner of the February SQL Aloha Contest
- February Question: Share with us your “best” restore disaster stories.
- July Question: If you could pick your manager, what ideal qualities would he or she have?
RSS
Twitter
Linked In
For the SQL systems I am administering, the business day (unlike the one of many others these days I am sure) is fairly well defined: 8 AM to 7 PM. We run a few transactional databases and then some databases that drive web site content. We have a different backup strategy for each. The transactional databases are updated much more frequently, so they are backed up more frequently than the web site databases. In addition, loss of data in the transactional databases would have much bigger consequences.
For the transactional databases, we run a full backup every day to tape (at 9 PM). There is also a full backup every 4 hours to disk (SAN) and every 30 minutes a transaction log backup to SAN.
For the web site databases, we have a full backup every day to tape, but no other backups. Their recovery model is set to Simple, so transaction log backups wouldn’t even make a difference.
Full Backup daily in the morning: 5 am
Log Backup all day long: The interval depends of the database use . The most critical database has log backup every 7 minutes
For production databases we have transaction log backups every half hour. Some servers write directly to a remote server and some write locally to have a script immediately shunt them off to get around some network issues. Full backups are done every night. For most test databases we just do nightly full backups but some that have configuration being done for projects or pre-production databases getting close to going live are treated as production.
We decided that the raw backups and restores were too complicated for our Networking team to understand… so we ended up buying RedGate backup so they could get a nice GUI to play with and make sure we are all on the same page with out backups and restores work (I can’t be there all the time). We do a full backup once a day with a copy going to the network location for Tape to pick up. We do Transaction Log backups every 10 minutes. Since we replicate our most critical tables/data, we (again, me and the networking team) agreed that this gives us a reasonable backup strategy that we all can understand and follow. I wish we were actively (read: automating) testing our restore process to ensure good backups and to offload CheckDB, but you can only win so many official battles at once…. and the good admins know to turn a blind eye to the “after hours” usage occurring on one of the non critical boxes… if you know what I mean.
On the 2005 cluster we have std sql backups, major dbs get full nightly diff at 8, 12, 4 and log every 30 mind. None business essential just get full backups nightly.
On the 2008 cluster we use dpm 2010 which from the quick look I have taken (backup team set it so far. It does full backups of everything at night then log backups every 15 mind. All day everyday. Its one of my jobs this week to look at, as with the backups on the setting they have filled the whole dpm san
Full Backup: 2:16 AM (VLDB – once a week, otherwise, every night)
Differential Backup: 5:03 AM and 12:03 PM
Transaction Log Backups: Every 10 minutes
We have SQL Server 2008 R2 and utilize backup compression and the backups are on a SAN.
We utilize SQL Server 2005 Standard x64 Edition, and our on our production systems I follow this regime:
FULL Backups (All Databases) – Friday evenings
DIFFERENTIAL Backups (All Databases) – Saturday through Thursday evenings
LOG Backups (Client databases only) – Every day hourly
Since we use log shipping to keep our data centers in sync, the backups are compressed and encrypted with SQL Backup and stored to a central network share in our primary data center. These backups are then copied to a secondary datacenter via hourly Robocopy scripts. In the secondary datacenter these backups are restored to SQL servers in Standby/Read-Only mode.
Full backups nightly (2 am), hourly log backups for most servers. Our OLAP servers are Full Wednesday and Saturday with differential on other days.
Production backup schedules are categorized.
1 Full a week, diffs every night, t-logs every 15 during the critical periods.
Less critical systems get a full a week, and 3 diffs a day.
Non prod servers get one full a day retained for one day. (Disk is not cheap
For SQL we run Netbackup Server with a HP tape library and LTO-4 tapes. the biggest problem with tape is the time it takes to mount a tape. to get around this i organized my backups by database size and made sure that similar backups go on the same tapes.
around 5pm the smallest databases get backed up, those that are less than 1GB in size.
a few hours later i have a few policies set up to stagger the backups for different servers so that there aren’t too many jobs in the queue at any one time. the last backups to kick off around midnight are the largest databases where the differentials are 100GB or more in size.
I got this idea when we still had our old tape library and found out that it improved backup times by 20% or so.
the small databases get full backups every day just because it’s easier than to have thousands of tiny differential backups
everything else is full backup on weekends and differential backups 6 days of the week. we don’t run any log backups but we have SAN snapshots run every few hours as well
played with disk to disk backup but it doesn’t play nice with SQL. we didn’t get that good of a dedupe ratio to make it cost competitive over tape.
part 2
one thing i don’t like about netbackup is the reporting. so i made my own. i dump the backup tables from MSDB to a central table and run SSRS reports off it. i have a few subscriptions set up to email me anything that has never been backed up, no backup in a week, backups for groups of servers, etc
We have over 50 production SQL Server instances (SQL 2000, SQL 2005, SQL 2008, SQL 2008 R2) and I use maintenance plans on all production instances with our default plan being:
Full backups once a night M-F.
Transaction log backups every hour from 7am to 6pm (working hours)
We retain at least 2 weeks of backups online (SAN) and 6 weeks of backups on tape.
We take this approach to backups to ensure we have backups of everything but also because we are dealing with over 50 SQL instances and 1 DBA so I need to automate as much as possible and have it notify me on any issues.
For the larger user databases: 1 full backup and the beginning of the week + 1 log backup every X minutes (with X between 5 and 60) + 1 differential backup every night.
Each one’s compressed natively (through the compression SQL Enterprise’s feature).
If I get to choose and we have the resources, I like to set up nightly full backups with transaction logs that run at least hourly. Of course, it always depends on the environment. Dev/QA tend to get backed up less frequently, maybe weekly and are often set to “Simple” recovery mode because they aren’t quite as essential for restores in most cases. We also have a “Pre-Production” environment that is mostly a copy of production. We back up the objects that are necessary to keep for that environment differently, but we can restore from Production in most cases and we’ll be good. We often use Snapshots there when testing our releases.
Current place is using MS DPM for backups, which makes things a bit more challenging when it comes to restores, but apparently saves on space. I really prefer either SQL Native backups or a tool dedicated to SQL Server Backups. They are easier to use for SQL Server and some of them have ways of doing an object-level restore without restoring the entire database. (Sometimes I miss that in SQL 6.5 and prior).
Ultimately, I’ll fall back on the “it depends” answer. I’ve had really small databases where it made sense to just do full nightly backups or even weekly backups if they were relatively static. I’ve also had databases where Full Nightly backups each day don’t make sense and Differentials will make more sense.
For testing, one company for which I worked was good about taking a time each year to go offsite to a special setup where nothing was configured and set up everything so it would run, including file server/database restores, installing applications, configuring Citrix, etc. That helped us to know that we could get running if we had a disaster in our area that threatened extended downtime. Not everyone has the budget for this, but being able to restore is an essential part of the test. The flip side is realizing that you have _no_ good backups right when you need them. That’s never a fun time and can definitely be a career-changing moment.
Cross my fingers and hope it doesn’t happen to me.
+1 Paul.. Just look at the person next to you and say..Was that not your Job.
Seriously. Daily 4 hourly backups using SQL Backup tool. Backups are then backedup nightly with a full backup of the system online with a diff.
I have used many different strategies over the years for SQL backups. Most common is a daily full backup in an environment small enough to complete the task overnight. Other large environment we do weekly fulls on the weekend with differentials daily and log backups throughout the day. I am a fan of third party tools to help improve the process and allow for central management of the backups for all servers in the environment. My favorite is RedGate SQL Backup Pro.
I have a fairly simple strategy. for user databases I do weekly full backups Starting Monday at midnight, Daily Differentials and half-hourly t-logs.
I then have a script that moves the backups off of my SQL Server onto a Backup Server that moves the data offsite (either tape or online).
System databases get a daily full.
I keep 14 days of backups online locally, 1 month on the backup server, and we keep the tapes for many years due to my industry.
I know this is going to read like a plug for Red Gate, but I can’t help it. Ever since I discovered RG’s SQL Backup, I have never even once used the MS version. In one particular client site, an event-package travel agency, inventory was very fixed (i.e. we had say 500 tickets to a U2 concert in Miami, and could not purchase more). We had five offices in various cities and every salesperson had to know exactly how many tickets remained at any given moment. I used SQL Backup to back up the database on the fly 8 times a day (the branches were located all across North America, so time zones were a factor commanding respect).
Once in several years of operation, we had a problem, and I had to restore the database. Within a few minutes, we were up and running again.
I should add that as an additional precaution, a scheduled job copied the backups to a DVD once a day. All I needed to do was replace yesterday’s DVD with a new one every morning. The rest was automatic.
Arthur
In the internal environments that I administer, the most comprehensive backup strategy that I take is in the staging environment which simply includes taking Full User SQL native backups twice a week and retaining the oldest one for 2 weeks. After that 2 week period the oldest one is automatically deleted by a job and is replaced by a new backup. I take a full system backup once a day and retain that for 3 days. We have no need for transaction log backups in our internal environment. Also backups are strategically spread across servers in case system outages and data loss occur.
For almost every database, I do fulls daily, and tx logs anywhere from hourly to every 15 minutes. I have several dbs that run in simple model. They get daily fulls. One is a large archive (1 table) that gets a couple hundred thousand rows imported from a .csv daily. For that one I do full weekly, daily diffs and keep 30 days of the import files so I can always rebuild it. ALL my backups go to network shares, which are replicated to matching offsite servers. Because I use Red-Gate’s SQL Backup Pro to compress my largest databases, I am able to keep 30 days of backup files on spinning disk! Full recovery model dbs get their Full backup after midnight and the Simple’s before. That allows me to spread the writing to my shares from about 7pm until 4am or so. The last backup of each month is also copied to an “end of month” folder and are kept on tape longer than the dailies. I’m not sure what the tape retention policy is, that’s for the SysAdmins to worry about. For my SQL Backup Pro servers I set the job up to keep 23 hours on the local disk and 30 on the remote. My smaller dbs using native tsql get backed up directly to the network share. Their backup jobs have a File Maint step that maintains the 30 days with a vbs script.
Do we get special consideration for using SQL Backup?
Regardless, I recently deployed it in production for my new employer and find it to be working rather well. I use it to create a nightly full backup of all production databases, as well as a differential every six hours. Transaction logs are backed up hourly for non-critical database and every 15 minutes for the mission-critical ones. Backups are written locally and copied to a remote server, where a third party replication tool synchronizes them to an off-site location. The complete backup chain for all production databases are retained on disk for 8 days. An incremental tape backup is executed nightly and the tapes are in an off-site rotation.
Log shipping is configured with SQL Backup to restore the logs for the critical databases every 15 minutes with a 5 minute delay with respect to the log backup schedule. We also have automated processes to periodically restore database-level backups to the standby server for all production databases which do not participate in the log shipping configuration. I also restore a copy of every database to a third system at least once a week, mainly as a way to ensure that the backups are tested and usable.
My current company’s (as today is my last day) backup strategy is using Symantec NetBackup SQL Client. I am not a fan of NetBackup for several reasons. 1. As the SQL DBA I am not a Backup Admin, therefore I have no control over backups, restores, configuration or testing. Our Backup Admin refuses to test restores on a regular basis because he has “faith” that NetBackup works. As Grant said in his webinar yesterday, “Restore, Restore, Restore” (reason #1 why I am no longer with this company) 2. Restore times are ridiculously long. A simple restore of a 20 MB database which takes 5-10 seconds natively takes almost 10 minutes using NetBackup. I am fearful what would happen when they have to restore the 150GB database. 3) Using NetBackup, there are no options for CHECKSUM or other maintenance tasks that could be done during the daily maintenance window, so more jobs have to be scheduled to accommodate. Our daily maintenance window is pushing 2 hours on some servers because of the time it takes to backup.
OK I have complained enough about NetBackup, truthfully in an ideal world, I would flat file backup daily (or TL backup if needed) then offload those files to the NetBackup server using the file backup client at the end of the week. Backup and Restore times would be greatly reduced and control of the backups could be managed by the DBA (which I foresee me not being replaced so it would fall to an future “accidental DBA”).
Full backup daily for small databases
Differential backup daily for large databases
full backup weekly for large databases
<
fullbackup weekly
differential backup daily
hourly transactional log backup
We take data loss very seriously or as an exec put it, “We don’t want to lose anything!”. With that in mind, we do Full backups using SQL Backup tools nightly to a NAS. Additionally, we have a SAN DB Server Snapshot nightly. Business Rules also require us to Log Ship every 5 mins to a different physical location. Additionally, we replicate some vital data. We’re soon going to add SAN Replicaiton.
It may seem like a little overkill but if we go down, I’ll sure be thankful.
Our Online backup strategy is :
DB Level Failover (Asynchronous Database Mirroring with no witness)
Failover Cluster isn’t necessary for us
and for Offline backup:
1- Full Backup every day every 6 hours and Copy to another machine
2- Differential Backup every day every 30 minutes and Copy to another machine
3- Tran Log Backup every day every 5 minutes and Copy to another machine
4- Daily Log Shipping with Server in Other Building (For Fire and …)
5- Transfer Full Backup every 15 days to FTP in another country (For earthquake and …)
6- Monthly Archive Full Backups with Tape
Backup strategy.
User databases: full backup weekly, differential backup daily.
System databases: full backup daily.
DR strategy:
Depends on the update frequency in the database, it’s a combination of snapshot and transactional replication – for some databases, log shipping every hour or backup copy and restore daily – for other databases.
At company we are dealing with a huge database (about 1 terabyte) which causes all the conventional backup/restore to be be completely useless.
1-at the the end of the weekday (4 pm) a full back up of all primary as well as secondary file groups
2-at the end of workday, differential back up of all the file groups
3-every 30 minuets taking transaction log backup
all the above backup file are getting on SAN storage and we regularly move them to Tapes ,the above strategy works great for us since it allows us to have piecemeal restoring.
For System databases like master and msdb: full backup daily.
We have a small CRM systems, we back up our databases with a fulll backup everyweek and anytime we update the schema of a database.
Currently: Full backup every day at 2 a.m.(stored up to 3 days). Also image of the server is taken by SAN admin(also stored up to 3 days – 6 days in total).
This strategy actually sucks.
I want: full backup of all user databases is taken every week on Saturday 2 a.m. Then all week (except for Saturday) same time differential backup is made. Also every 30 minutes log backup. msdb is back-upped when there is an appropriate change made. model – never touched it even with a long stick. I forgot to mention my recovery model, but if you are reading this you know what that is.
Hi Brad,
Is this a trick question ? I’m sure I heard you say at your recent SQLBITS presentation that you shouldn’t have a backup strategy, but a restore strategy !!!
anyway…we have a number of backup/restore strategies in place.
1) Full backup taken every night. and stored on the local server. Backups are copied to a test server where a restore is performed. The test server is verified each morning by our sys admin.
2) Transaction log backups taken every 3 hours during business hours. These transaction log backups are also stored on the local server and then copied accross to the test server.
3) Mirroring from production database to a backup production server.
4) System databases have full backup taken once a month, stored locally on the server and then copied to the test server.
Your blogs are great, keep them coming !
There have been a couple of questions I have received about how the winners of this contest is selected. While the answer to this question are included in the instructions above, I want to emphasize that all winners are selected randomly using the random generator function in Excel.
Thanks for everyone who entered this month’s Question of the Month. The randomly selected winner is Cayton, who will receive a $50 Amazon.com gift certificate and a license to SQL Backup Pro. Be sure to enter the June 2011 contest at: http://www.bradmcgehee.com/questionofthemonth/.
I totally agree with the respondent who declared that Backup Strategies are unimportant; Restore Strategies are the most important thing. And given multiple servers, replication, etc. Restore Strategies are ever-more important.
This leads to another scenario, of which I fear too many of my colleagues are unaware: Restore Strategies are theory until they have been validated. In other words, periodically you must purposely backup the db and then take it down and then restore it from the penultimate backup, and if that doesn’t work, your Restore Strategy has issues.
All this can be done without interrupting the flow of business transactions, but to make it work you have to have the correct setup, and if management won’t buy into it, revise your resume and start looking for a new position. At minimum, there need to exist three servers (possibly clustered) — Production, Development and Testing. This frees you (DBAs and developers) to experiment wildly on the second and third, without violating Production.
I’ll supply one example, when I worked for a company in Bermuda, which firm had the intelligence to set it all up correctly. First thing I did was create a scheduled process that backed up the real db and copied it to both Test and Prod; that way I had the (relatively) most recent data. Then, on Dev, I could rewrite sprocs, run them, and check the results. Once I was satisfied on Dev, I copied the db to Test and re-ran all the changes. Once that worked, I was finally confident that I could copy the results to Prod.
I might add to this one more proviso, which I learned from one book or another, I can’t remember: for each test, write a sproc guaranteed to succeed and another guaranteed to fail (e.g. pass an int where a string was expected), so it’s all done there and then and all that remains is to run the sprocs and see what bleeds.
Arthur
Kieron Basquille: You are right, I do preach that you should have restore strategy, not a backup strategy, but given the few words I was allowed to this question, with little opportunity to explain what I meant, I had to take a shortcut.
Hi all,
We try to apply same strategy for all instances.
Based on a backup window (=start + end) so that DBAs and backup admins have an agreement as for when tape backup should start (after SQL backup is over).
- Sunday : Fullbackup, between 2 and 6.
- Monday to friday : Diff backup, between 23 and 1
- 7/7 between 8 and 20 : log backup every hour.
David