Backup Concepts
Appendix C: Backup and Recovery Best Practices 117
■
Changed the arrangement of files among the FileGroups
■
Changed the arrangement of tables among the FileGroups
■
Changed the partition scheme of a Partitioned Table or Index
■
Canceled a full backup job while it is running
■
Modified the database definition, including adding or removing a table, or
changing the definition of a table
■
Added or removed an index
■
Restored the database from backups
Database Consistency Checks
When your database activity is low, you should run a database consistency check
(DBCC), particularly with a large database. Although it takes some time, it is
important to determine that your Microsoft SQL Server database is functioning
well.
A DBCC tests the physical and logical consistency of a database. When you
enable the Database Consistency Check option for a backup, the DBCC performs
the following tests:
■
DBCC CHECKDB
: Checks the allocation and structural integrity of all objects
in the specified database. By default, the CHECKDB performs a check for
indexes that can increase the overall execution time.
■
DBCC CHECKCATALOG
: Checks for consistency in and between system
tables in the specified database.
For more information, see Database Consistency Check (DBCC) Options.
Backup Strategy Recommendations
To establish a good backup strategy, follow these recommendations:
■
If your database activity is low to medium, we recommend the following
pattern:
–
Full backups: once per week
–
Differential backups: once per day
–
Transaction Log backups: every two to four hours
■
If your database activity is high and your database is of small to medium
size, we recommend the following pattern:
–
Full backups: twice per week
–
Differential backups: twice per day
–
Transaction Log backups: every 60 minutes
