SQL Server databases can be dumped to disks or tape drives. It is a good idea to keep not only the most current dump, but the previous n dumps as well (n depends on what you judge to be necessary). To illustrate the need for this, assume that some failure occurred that resulted in your need to reload the database from a dump. If only the most current dump was kept and something destroyed that dump, you would have to attempt to reconstruct your data from scratch. Or say that a user accidentally issues a misqualified update or delete statement against one or more tables. This can result in the incorrect modification or loss of large amounts of data, which could go undetected for several days, weeks, or more. By keeping a succession of previous dumps, you have multiple "safety nets" in place for recovery.
If you dump your database or transaction logs to the same disk or tape dump device each time, the current dump can overwrite the existing dump file or be appended. SQL Server 6.0 allows you to specify whether the new dump should overwrite or be appended to the existing contents of the disk or tape dump device. This allows you, for example, to circulate tapes for reuse in a least-recently-used fashion. SQL Server provides an expiration parameter that helps to ensure that a new dump does not overwrite an existing dump too soon. SQL Enterprise Manager can be used to specify the number of days, or a specific date, that must pass before an existing dump can be overwritten by a new one.
When a database is dumped, SQL Server dumps not only the data portion but also the transaction log for that database. The transaction log must be included in the dump so that when the database is later loaded, transactions that were in progress during the dump can be properly rolled back. Because the transaction log is dumped with the database, the resulting dump file may be larger than the data portion of the database. For example, assume that you have a 100-MB database with a 25-MB transaction log. If both the database and log are nearly full, the dump file would be approximately 125 MB when the database is dumped. Be sure to allow enough room on the media you are dumping to for both the data and log portions of the database being dumped.
The size of the dump file will always be the correct size of the amount of data in the dump. For example, assume again that you dump a database to the DB_DUMP device and the resulting DBDUMP.DAT file is 100 MB. If you then dump a 5-MB database to the same device, the size of the DBDUMP.DAT file will have a size of 5 MB (or less, depending on the amount of data in the database).