It is best to keep live data and the backups on separate computers or, better yet, in different buildings. If the databases and/or logs are dumped to physical 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 the 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 quickly get to them 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, the backup of the data will remain safe.
The frequency and type of backups you do will generally depend on two factors: the acceptable amount of work that can be lost due to media or other failure, and the volume of transactions that occur on the SQL Server. For 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 might 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 insurance of recovering needed data.
If dumps are performed online, they should be scheduled for times when the server is not being heavily updated, because the dumps will slow SQL Server somewhat. 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 non–SQL Server related work during that time.
Mirroring adds another level of data protection. With mirroring enabled, data that is written to a database is automatically duplicated on a separate device or drive. Should a media failure occur on either, the damaged device or drive is unmirrored, and the undamaged device or drive automatically takes over writing all transactions. Depending on the number of disks and disk controllers present in the computer, it is possible to have a configuration in which any one disk can fail and SQL Server will continue to run uninterrupted. Combining mirroring along with a regular backup schedule can help ensure a high level of redundancy and minimize the risk of data loss. For information about Windows NT–based mirroring, SQL Server-based device mirroring, and other methods of implementing redundancy, see Chapter 7, Managing Drives.