The aim of this post is give users some insight on how to check whether an SQL backup and restore strategy is functional. The post also explains how administrators can check whether or not the system is disaster ready.
1．Managing Backup Media
There are certain provisions that every backup plan designed to recover SQL Server database must have. The provisions are aimed at managing backup media which are described below:
- A plan for overwriting backup media.
- Management and tracking plan for recycling and storing all the backup sets.
- If it is a multi-server environment, then there should be provisions for decisions to be use either distributed or centralized backups.
- Provisions for tracking useful media life.
- Ways to minimize the impact of loss of backup media or back-ups such as losing some important tape.
- Provisions for how to store backups on offsite or on the set. Analyzing what effect it will have on the recovery time.
2. Running Base-Functionality Scripts
Base functionality script is run as part of disaster recovery plan. The purpose behind running this script is to make sure that everything is functional and working as required. The script offers a dependable mechanism for the database administrators to make sure that the database is back to the right state. They can do that without obtaining a confirmation or depending on end-user for verifying the same.
Base-functionality scripts happen to be application specific. Meaning, they can take various forms depending on the environment. For instance, in a reporting system or decision support, the script can turn out to be only a copy of multiple key reporting queries.
In case of an OLTP (Online Transaction Processing) application, the script can go on to execute a stored procedure that execute UPDATE, DELETE, and INSERT statements. A base functionality script can be something as simple as an SQL file that fetches the SQL statements from sqlcmd utility and sends it to the server. One more way is to use a .bat file which contains sqlcmd and bcp commands.
3. Checking the Disaster Readiness of the System
It is important to test the system for its readiness to handle the impact of a disaster. For that, it is recommended that the admin periodically perform following steps to check the disaster readiness of the system:
- Do carry a test of your backup and recovery procedures using various scenarios before a real disaster takes places. Testing is the most important part because it tells you whether or not you have the necessary backups to undo the damage of different failures. It further helps in establishing that your recovery procedures are well documented and clearly defined. Most importantly, they help you find out if they can be executed quickly and smoothly by a qualified operator.
- Keep performing regular transaction logs and database backups to decrease the amount of data loss. Do back-up both the user database as well as the system.
- Do maintain all the system logs securely. Keep track of service packs installed on SQL Server and Microsoft Windows. Other than that, maintain records of network libraries and security mode. Additionally, if the server is running in SQL Server and Windows Authentication Mode, then keep the password in a safe and secure location.
Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com