When to Back Up

You should back up databases and transaction logs frequently, and you should save the backups.

The frequency of your backups and the amount of time that you should save them depend on your database application. As an example, the following is a typical schedule:




When to perform the backup	What to back up	Minimum time to keep backup

Every day	Transaction log	Two weeks
Every week	Database and transaction log	Two months

In addition to routine backups, it is important to back up a database:

After initially creating the database.
After performing a nonlogged operation.
After creating an index.

The reasons for backing up a database during these times are summarized in the following sections.

After Creating a Database and Regularly Thereafter

Each database should be backed up just after it is created, and on a fixed schedule thereafter. For example, if you create a database on Monday and wait until Friday afternoon to back it up, you risk losing a whole week's work if there is a media failure on Friday at noon.

Transaction log backups made between the creation of the database and before the first database backup cannot be used, since you must load the transaction log after you load the database.

After Performing a Nonlogged Operation

In addition to backing up each database on a fixed schedule, you must back up a database any time you perform an operation that is not logged. For example, back up a database:

Every time you are forced to execute DUMP TRANSACTION WITH NO_LOG or WITH TRUNCATE_ONLY (because you have run out of disk space for the transaction log).
Every time you use non-logged WRITETEXT or UPDATETEXT, or SELECT INTO on a permanent table, and fast bulk copy (because SQL Server does not log the data insertions made by these statements, there would be no way to recover them in case of media failure).

After Creating an Index

You should back up the database each time you create an index. The pages that are written when a new index is created are not recorded in the log; instead, SQL Server relies on LOAD TRANSACTION to re-create the index in case of media failure. This does not mean that CREATE INDEX is unrecoverable but that SQL Server takes just as long to recover the index as to create it in the first place. Since you use LOAD TRANSACTION after a media failure, it is better to have backed up the database so that you do not have to wait for the index to be rebuilt.