SharePoint SQL Disaster Recovery – uh oh!
You may have a nice warm satisfied feeling from knowing you have SharePoint .BAK backup filesets of all your SharePoint Content Databases. You may even have already accepted and prepared that a rebuild from Farm Configuration backup may actually take way too many hours – and be unfeasible to keep the exact same named servers and database names. You might have even consolidated and prepared your development and/or staging platform to maximise licencing costs as well as keeping it only patched to the same or perhaps just one higher than the production environment, so that restoring backups can be done in reality without the sudden misleading (even after checking Event and ULS Logs) “DirectoryNotFoundException”, let alone “UnauthorizedAccessException” errors throwing everybody into panic stations – which makes you realise that all that time, the backup .BAK files were, in fact, misleadingly useless!
Often, a SharePoint .BAK restore is treated “as given” when it is very far from potential success – you’ll only really know when you’ve fully verified actual restores. Including when you’ve done it on a completely new Farm (and possibly even Domain) – because restoring to another Site Collection on the same Farm is easy, but you are unlikely to have that luxury in a disaster. But because of high licencing costs and server resources, that’s exactly the only kind of restore that many administrators are able to test.
If you’ve already covered all this, you probably also realise that it’s also often difficult to restore a raw SQL Database, even when stopping all the SharePoint Timer jobs (with a PowerShell script which generates a list of all those jobs but only for the particular Content DB) and then killing any SPIDs in the DB itself. That’s why many advise remounting the database and log files directly, but that’s not always possible if the files are corrupt or not accessible. SQL Backups are handy (but underrated, in SharePoint) because they are often made off-server, so they will be accessible after that disaster. But even they are not always easy to restore unless you already know how and have tested.
Full testing outside of the easier live environment is critical to proper Disaster Recovery – particularly with SharePoint, which has a simple but misleading .BAK backup function. You don’t want to be learning the ins and outs of all these details in a critical situation, waiting hours for a restore which will never work… so get onto it – testing is believing!
