Performing backups is routine for most DBAs. The ability to successfully perform a restore is not as routine, but almost more critical. One significant issue that can impact the success of a restoration operation is corruption of database dumps. When disks are involved, this type of corruption isn’t as common; but even disks can go bad and when they do, corruption can happen, and corruption can affect database dump files. Even if the backups don’t involve disks, the database dumps can become corrupt during transfer from one medium to another, such as from tape to another server.
Perform regular restorations
So, how can you confirm the reliability of a SAP Sybase Adaptive Server Enterprise (ASE) backup dump? The very best way is to perform an actual restoration (note, though, that a corrupt database that gets backed up will be a corrupt database when it is restored). If you’re already doing this on a regular basis, you’re well ahead of the curve. If you’re not doing this on a regular basis, it’s time to start.
An easy way to work this into your schedule is to look for an opportunity every quarter to restore your databases. You can, for example, schedule restorations into a development or test environment to refresh the data there. When you do this, get the benefit of confirming that you can successfully restore from your backups (and confirming the success of this part of your disaster recovery approach).
Use dbcc checkstorage regularly
Make sure you are running dbcc checkstorage against your databases before they get aged out, and depending on system and resource availability, before they are backed up! We recommend doing this regularly, preferably every night. When you check regularly, you can stop a small error from becoming much bigger.
The dbcc checkstorage command is extremely useful in a number of ways, because it:
- Includes many of the checks found in other DBCC commands
- Doesn’t lock tables or pages for long periods of time, which allows DBCC to discover errors without interrupting udpates
- Scales linearly with the aggregate I/O throughput
- Separates checking from reporting, which helps to provide a detailed description of space usage in the target database
- Stores activities and results in the dbccdb database, which you can use to investigate storage trends on the database
Before you start using dbcc checkstorage, however, you will need to ensure the server is optimally set up for using it. Resource planning, server tuning, constructing the dbccdb database, and configuring the workspaces are essential to successful and speedy completion of the command. Refer to the Administrator’s Guide to Sybase ASE 15 by Jeffrey Garbus and Ashish Gupta for detailed information about setting up and administering checkstorage.
Planning restorations is as important as planning backups
A robust disaster recovery approach must include regular testing of backups. No one wants to find out their database dumps are corrupt and unusable only when a database failure has already happened. Take the time to schedule regular restorations (at least quarterly) to verify that you can restore successfully. If all goes well, you will only use your backups during these regular test restorations. But if things go bad, as they often do, you’ll be glad you know you can trust your backups.