Database Design and Backup Strategy

With small databases, the transaction log is usually stored on the same database device as the rest of the database. DUMP DATABASE backs up both the database and the transaction log, after which you can use DUMP TRANSACTION WITH TRUNCATE_ONLY to remove committed transactions from the log.

With large databases, the transaction log (the log portion of the database) and the data portion of the database are usually stored on a separate devices. In these cases, you can use DUMP TRANSACTION to maintain a backup of only the transaction log. Dumping only the transaction log uses less storage and takes less time than DUMP DATABASE, which backs up both the transaction log and the database. Dumps of the transaction log are usually coordinated with DUMP DATABASE as part of an overall backup procedure. Typically, transaction log dumps are done more frequently than database dumps. To recover a database, load the most recent database dump and load all the transaction log dumps (in order) made since the database dump. The database is restored to its state at the time of the last transaction log dump.

Regardless of database setup, it is highly recommended that you always run DBCC CHECKDB, DBCC CHECKALLOC or DBCC NEWALLOC, and DBCC CHECKCATALOG on a database just prior to dumping it. DBCC will check the logical and physical consistency of the database. If a database or transaction log that contains errors is dumped, the errors will still exist when the data is reloaded. Under some conditions, this can even prevent successful reloading. If any of the DBCC commands report errors in the database, contact your system administrator or your primary technical support provider.