| Executive Summary: Follow these standard best practices for SQL Server backup and make sure that your system restore goes according to plan by performing full backups daily, backing up the host OS, and practicing your backup and recovery strategy. |
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
Christan
Humphries
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.) |