Where and When to Back Up

It is best to keep live data and backups on separate computers, or better yet, in different buildings. If databases and/or logs are dumped to disk devices, it is often optimal from a performance standpoint to dump them to local hard drives on the server, as opposed to drives on some other computer on the network. However, from that point they should be copied to tape or to another server so that data and backups are not on the same computer. For convenience, most people choose to have their backups close at hand so that they can get to them quickly if needed. If this is done, keep another copy somewhere offsite, such as a bank safety deposit box, so that in the event of damage to the building, backup of data will remain safe.

The frequency and type of backups you perform will generally depend on two factors: the "acceptable" amount of data that can be lost due to media or other failure, and the volume of transactions that occur on the SQL Server. At many sites, databases are dumped weekly and transaction logs are dumped daily. This can vary widely, however. For systems that have little update activity and that are used primarily for decision-support, only weekly database dumps may be needed. For other high-volume online transaction processing (OLTP) environments, databases might be dumped daily and the transaction logs dumped hourly. The strategy chosen should be one that best fits your environment while providing adequate assurance of recovering needed data.

If backups are performed online, they should be scheduled for times when the server is not being heavily updated because the dumps will slow down SQL Server. In addition, the dumps should be issued on a fixed schedule. By using a fixed schedule, users will always know when the dump is occurring and can expect a slight delay in performance, or they can plan to do other work during that time.