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 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.) |
