SQL Server Backup Best Practices
The absolute worst time to find out that your recovery plans don t work is right in the middle of a critical system restore. Follow these SQL Server backup best practices to ensure that you really can restore your system when (not if) it goes down.
Perform Full Backups Daily
A full database backup is the foundation for every DBA s data protection plan and in most cases should be performed daily. SQL Server supports online backups, allowing end users and SQL Server jobs to be active while the backup operation occurs. Even so, large databases can take a long time to back up. Strategies for reducing the backup window include backing up to disk and utilizing backup data compression.
Perform Frequent Transaction Log Backups
Next most important is to back up the transaction log, which contains all of the recent activity in the database and can be used to restore a database to a given point in time. Backing up the transaction log also truncates it, keeping it from becoming full. Like database backups, transaction log backups can occur while the system is active. Organizations with active databases might back up the transaction log every 10 minutes while those with less active databases might need to back up the transaction log only every half hour or every hour.
Regularly Back Up System Databases
Your backup strategy is incomplete without a plan to back up SQL Server system databases (master, model, msdb). These databases contain system configuration information as well as SQL Server job information that needs to be restored as part of a total system restore. Back up system databases daily for frequently changing instances, weekly for more stable installations.
Back Up the Host OS Daily
SQL Server runs on top of the OS and an event such as a hardware failure could require a complete system restore, beginning with the OS. Therefore, daily backups of the host OS are a good idea. At a minimum, back up the host system partition following any system updates or configuration changes.
Practice Recovery Operations
Changing business requirements can affect your plans, quickly making backup strategies obsolete. Test your strategies regularly in different scenarios, including both system and individual database restores, to ensure your backup plans really work when you need them.
YOUR SAVVY ASSISTANT
The Missing Link to IT Resource
For her birthday, I gave my sister a card embossed with golden print that reads The economy stinks. Be happy you got this card. However disappointing the birthday gift (and my attempt at a joke) most likely was, the shiny message on it is accurate. And in this economy, which has forced companies to nudge and sometimes shove employees out of positions, I ve noticed a change in attitude toward job hunting. Here in the United States (and even in countries with better economies), it seems that changing jobs is not only accepted, it s almost expected. And just like on Match.com, It s OK to look. In fact, our network of IT products includes a free resource that even makes it easy to look: IT Job Hound.
IT Job Hound is an online job-search engine that concentrates on the IT industry. Job seekers can find recently posted positions from top IT companies on the site or via email job alerts no registration required! Whether you want to evaluate your skills, secure a new position, or completely change your job title, check out IT Job Hound at www.itjobhound.com. (If you re looking for gift ideas, give my sister a call.)
Mike, I am being pressured by the IS Director to use CA Backup Exec (or the like) in lew of regular SQL Server backups. I tried to explain how Full, Differential and Tran log backups are used to restore to a point in time by identifying T-SQL transactions and log sequence numbers. He insists that there are third part tools available that are easier to use (not a good reason in my book!) and continuously take pictures of changes. What is you take on said tools and which, if any, would you recomend. tnx, G. Douglas Clavell Seven Feathes Hotel and Casino Canyonville OR.
frontierteg (not verified)
First of all, I’m assuming you are always backing up your SQL server to disk and the server people have already figured in the space requirements and have allocated that space to you. Remember, they are part of your backup plan. With that assumption, like the article says, you should backup changes every 10-30 minutes in case of hardware failure. How often you create a full backup is really up to the individual business to decide. The questions become, how long can you stand to be down? Restoring a full backup and a days worth of logs takes less time than a full and a weeks worth of logs. As to being able to restore to a certain point, as long as you are keeping the last 2 weeks of full and/or log backups on tape, you shouldn’t have a problem restoring to any point. So the questions become, how often do I want to transfer the SQL backups to tape? and how far in the past do I want to be able to restore to? Hopefully you are backup up to a seperate server in case the SQL server goes down. And hopefully you are taking the backup media offsite at least daily in case the data center burns down. Or you have a backup SAN offsite that you just copy the backups to once they are created, again, in case the data center burns. All in all, a great article explaining the use of full backups and transaction logs. Use some common sense to implement the right backup scheme for your environment using seperate servers or portable media.
Why a FULL backup every day? You realize you limit your ability to recover to a certain point of time with every FULL backup you perform. Suppose we need to go back 4 days ago because the corruption was noticed until then? Now how are you going restore? You already toasted your full backup a couple of days ago. I prefer a FULL once a week. And differentials throughout the production hours. After a week, my data has been clean enough to go through another weekly full backup. If a user then calls me and says on Thursday, I need the backup from Tuesday, I can easily say sure which one, morning, afternoon or evening. Doing fulls daily limits your ability to restore via multi-days. And if you attempt to SAVE your DAILY backups, and you have a large database, well you’ll have the server dept calling you. Do a full once a week. Do diffs between that time whatever the business needs decides. You’ll see your diff backups are much smaller. If you have an error, backup the tranlogs and go from there. If you need to restore you’ve got a lot of choices. Same thing with system dbs. And I’d be REALLY surprised if you’re MAKING changes DAILY on jobs, schedules, etc, creating new or altering your schema, or your one tired puppy! The article doesn’t even tell you why you back them up. And far be it from actually RESTORING the system databases. Me I set mine to once a month, or heck once a quarter, knowing if I had to, I’d just re-intall SQL and restore because messing around with system dbs are no fun and causes extra headaches when I can do a clean re-install. Not much of a BEST practice article. I was expecting a little more from Otey. disappointing. Andy
Mark (not verified)
The most important thing about a backup is it works when you need it. To work it needs: 1) to be a complete backup set 2) current 3) supports a restore process that’s tested, works, and has contingency in it. When we set a backup schedule we look at what we’re trying to protect. We’ll reimage the OS in a heartbeat. So we only backup the OS weekly. If the DB has a warm standby, it only gets a full backup weekly and incrementals on the daily basis (plus the log backups during the day). More importantly than setting the schedule is confirming the backups are working. 1) did the jobs complete when expected? If they are late, it’s a sign your equipment is failing, or your requirements are changing, either require action. 2) are the restores working? You need to measure both of those things. Additionally, I measure the actions taken on #1 and #2. If the rate of action is changing. why..are we managing better or worse, and then what action should that drive. There’s a lot more to best practices for DB backup than setting a schedule.
Seems to omit the use of differential backups.