Unix Backup and Recovery

An Ounce of Prevention . . .

One common thread throughout this chapter and the other database recovery chapters is that recovering without the proper backups is, at the least, very difficult, and, at the worst, impossible. If you employ the following tips, you will have a better chance at keeping your system healthy and in a state from which it can be easily recovered.

Starting Out

• Run a full cold backup of the data server. Make sure to have both online bcp s of the system tables and hardcopy outputs.

• Run a full hot backup. Store a copy onsite and a copy offsite.

• Document all backup and recovery procedures.

• Make sure all databases have transaction logging enabled and running. A quick way to check this is to dump the transaction log. If it does not work, check the dboptions of the database making sure select into/bcp is not enabled.

• Mirror the system databases. This way more than one physical device will be covering the system databases. If one of the devices has problems, it can be taken out of the set automatically, and the system keeps functioning smoothly. If you can afford the disk space, mirroring other databases also will provide added security.

Day to Day

• Run a regularly scheduled automated backup procedure.

• Check the system error logs regularly. Take appropriate action as soon as possible when any error shows up.

• Run dbcc checks periodically to make sure the internal structures of your databases are OK.

After Major Changes

• Run a full backup of the system tables whenever you add devices, ...