|
|
|
|
|
6.5 Disaster Recovery Plan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 All databases should be dumped daily to a dedicated backup server for reliability and speed. These are native SQL Server dumps which is the safest, most robust, and least expensive backup solution for SQL Server. This gives the ability for your database servers to be backed up natively while also integrating the backups into an overall Enterprise backup strategy. You get the advantage of being able to rapidly restore a corrupt database or server from disk while also having media protection from the Enterprise backup. This strategy also keeps all of your backup tapes stored offsite as a group such that a full site recovery is much easier to coordinate. A very simple, high level diagram is included in Appendix E.Along with the disk dumps, three procedures need to be run on a daily basis immediately following the backup and the results sent to a file that resides in the same directory as the disk dump. These are: sp_help_revdatabase, sp_lti_gen_disk_init (Appendix B), and a BCP of syslogins. (The BCP of syslogins should actually be from a view that excludes sa, repl_publisher, and repl_subscriber.) With the outputs of these three procedures and the disk dump, we have a complete snapshot of the server that will allow complete recreation. The tapes should then be rotated offsite as per <Company Name> policy. In addition to the backups and tape rotation, DBCCs need to be run on a consistent basis across all databases to form the preventative maintenance that is required for every server. An enhanced script for DBCCs is attached as Appendix C. Backup Strategy <Insert your backup rotation here> The full backup to the local disk also generates four files for use in recovery. The first file called <filename> contains the output of sp_configure. This allows the resetting of all configurations options in the event the server needs to be rebuilt. The second file <filename> contains the output of sp_gen_disk_int. This contains a script to recreate any database devices. The third file called <filename> contains a script to recreate all databases with the same segmentation as originally designed. The fourth file called <filename> contains the contents of the syslogins table. This file can be imported into SQL Server via BCP to recreate all logins with the passwords intact should a rebuild of the master database be required. <Insert the Enterprise backup schedule to tape here> <Insert the offsite tape rotation here.> Corruption or disaster scenarios Notes One note needs to be addressed for any of these scenarios. You must be familiar with performing the command line options from isql/w for each of the recovery scenarios. In most cases, you can get by with using Enterprise Manager or some other GUI management tool. If you have to place the server or a database in single user mode, then you must use isql/w. Any other tool, especially Enterprise Manager, will normally create two or more connections and thus will throw error messages because only one connection is allowed. DBCC reports a 2540 error This is an extremely common error. 2540 errors occur when a page gets allocated for a process, the process completes without needing or using that page, and SQL Server does not clean up the page allocation. 2540 errors are also very common when a DBCC checkdb has been run without putting SQL Server into single user mode. This is a non-critical and can be safely ignored. A DBCC fix_al will correct this problem, but it should only be run if the following conditions are met:
Even if all of these conditions are met and you run a fix_al, it will only recapture a very small amount of space in the database which might only gain you a day or two for transactions.
6.5 Disaster Recovery Plan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.