|
||
|
INTRODUCTION Many backups are just a big, expensive, waste of space. Big in the sense that they take up lots of disk space, because while disk costs continue to decrease, storage requirements continue to increase and disk for servers isn’t free. Expensive because when it comes to recovering from a disaster, many backups aren’t viable, and most contain large gaps in the data they’re supposed to be protecting. Consequently, non-viable backups or backups with significant gaps can translate into significant down time or expense. Ensuring that backups aren’t a waste of space really only requires two things: a viable disaster recovery document, and regular testing of backups. The Importance of DocumentationDocumenting a disaster recovery plan can be a tedious, painful, process that many organizations ignore because other, more pressing, matters tend to require their attention on a regular basis. But without a documented recovery plan on hand, two large assumptions are being made. First, there’s an assumption that the necessary skills and resources will be on hand to recover from a disaster when it happens. Second, there’s an equally ominous assumption that even if those skills and resources are on hand, that everything will proceed during disaster recovery without any complications. The problem, of course, is that a disaster is, by definition, a set of complications. The benefit of a well documented disaster recovery plan is that it can address various possible complications, along with general escalation policies and approaches that can be used to deal with scenarios where things trend towards getting out of control. More importantly though, the existence of a well documented disaster recovery plan ensures that whoever is putting the plan together has run a few recovery operations and tests. This, in turn helps ensure the skills necessary to be able to quickly act (with confidence) after a disaster has occurred. In fact, as a consultant, I’m frequently amazed at just how many IT pros and even DBAs have never actually restored a database, or who don’t have a decent understanding of the underlying concepts and requirements. Or, as I like to tell many of my clients: “Do you really want to learn how to recover a database during a disaster when the CEO is standing over your shoulder?” Therefore, rather than waiting to learn how to recover a database once a disaster occurs, the benefit of a disaster recovery plan is that it implies that you’ve gone ahead and ‘cut your teeth’ on the ins and outs or recovering a database BEFORE a disaster occurs. Taking this approach allows IT Pros and DBAs to learn the differences between restoring a database and recovering a database , and also helps ensure that the locations of backups, log files, escalation requirements, and so on are all documented into a single place. More importantly, the creation of a documented disaster recovery plan pre-supposes that whoever is creating this plan is acquiring the skill necessary to be able to recover critical data after a disaster. The Importance of Testing BackupsDatabase failure is always a question of when it will happen, not if it will happen. And the purpose of a disaster recovery plan is to mitigate the costs associated with that failure when it occurs. But even if you’ve documented a disaster recovery plan (and thereby gained the skills necessary to recover a database), those skills won’t do you any good if your backups aren’t viable – or if you have large gaps in your coverage. Consequently, a critical component of any disaster recovery plan is that DBAs regularly test and verify that backups are working correctly – and that they can be used to recover a database. And there are two major benefits that stem from regularly checking backups. First, you’re ensuring that backup programs, schedules, and storage facilities are all working together as needed. This, in turn, helps avoid surprises. (We’ve all worked at places where something crashed and IT ended up looking like complete idiots when they announced that backups had ‘magically’ stopped working a few days or weeks before the crash. Regularly checking your backups helps avoid these kinds of surprises on your watch.) The second benefit that arises from regularly testing backups is that it keeps recovery skills honed. By regularly restoring copies of critical databases (either alongside production databases or out on different servers), you’re building familiarity with the recovery process – which will pay off well during a disaster. Likewise, by regularly testing and validating backups, you’ll gain quick insight into any gaps in coverage, changes in recovery times, or other problems with security or your environment that could complicate recovery during an actual disaster. A side benefit of this, of course, is that you’ll be able to keep your disaster recovery document up to date – which is a critical component in mitigating data loss and down time. Taking Backup Validation to the Next LevelThe problem, of course, with regularly testing and validating backups is that it requires time and energy. But, without doing so, you’re running blind. Consequently, regular testing of backups is one of the hallmarks of a good DBA. And, while regular, manual, tests should be implemented, you’ll be that much more ahead of your game if you’ve also got automation processes in place that regularly validate your backups. Leveraging automation should never be a replacement for regular, manual, testing – but it can be another tool in ensuring that backups remain viable. Setting up automation processes for backup validation can take some time – but it’s a worthwhile activity that can provide big benefits (both in terms of the additional coverage that it will provide in terms of validation, and in terms of the increase in skill-set that it will require). One cool option that does come to mind for quickly validating the viability of backups (i.e. to make sure they haven’t become corrupt) is to use Idera’s SQL virtual database. If you’re unfamiliar with this solution, it uses a console to let you specify a backup (or series of backups), point them at a SQL Server, and then specify the name of a read-only database that you’d like to attach your backup as. And what’s great about SQL virtual database, is that it takes mere seconds for your backup to become ‘attached’ as a viable SQL Server database that you can then query. That alone makes it a great tool for DBAs who want to regularly validate their backups (the caveat, of course, is that backups either need to be native SQL Server backups, or done with Idera’s SQL safe backup). However, Idera’s SQL virtual database also provides a full-blown command-line interface that can be used to easily script the process of attaching backups which you can then script queries against, and so on. CONCLUSION The take-away from all of this though, is that if you don’t have a disaster recovery document in place, or if you aren’t regularly testing your databases, then you need to make some changes. Likewise, if you already have a disaster recovery plan and are regularly testing, adding automated validation routines can help give you an extra bit of protection and alert you to any potential issues that might have rendered your backups useless. HOW CAN IDERA HELP? Idera SQL virtual database is a one-of-a-kind solution that lets you attach SQL backup files in seconds, without wasting the time or storage space necessary to actually restore the database. Once a backup file is attached with SQL virtual database, you can use any native or third-party tool to view and query the database, just like a real database. SQL virtual database makes the process of validating backups much faster and easier since there is no need to actually restore the backup files. ABOUT THE AUTHOR Michael K. Campbell (mike@sqlservervideos.com) is a consultant with years of SQL Server DBA and development experience. He spends most of his time engaged in consulting, technical evangelism, and creating free online SQL Server Videos @ http://sqlservervideos.com. |
||