Recently our group consider re-organizing the backup solution for all production/development database. This gave us a time to re-consider the SQL Server backup activity.
First of all, Recovery Models.
We have to fully understand the three Recovery Models of SQL server backup. Except the system database and development environment, normally the full mode should be selected. The details to explain this is as below:Recovery Model Overview
Second, Database Files and File Groups
Under those recovery model, SQLServer can perform database backup (full and differential), file backup (full and differential), filegroup backup and transaction log backup. So the concept how SQLServer organize database file/filegroups is the basic things to know, you can reference to here Files and File Group Architechture.
Third, understand the transaction log backup
You can reference here to overview the transaction log Overview, the transaction log physical structure transaction log physical architecture and the transaction log logical structure Logical structure. How to manage and working with the transaction log: working with transaction log. The transaction log backup is the must be requirement for Full recovery mode, but sometimes even the transaction log being backed up often, the log file still doesn’t shrink, the factors to impact the log file truncate can referenced here Factors to delay the transaction log truncation
Fourth, Backup and Restore Strategy.
You can reference here to overview the strategy of backing up and restoring the database. Backing up and Restoring
and reference here to see backup concept and overview the backup considerations Backup Overview and reference here to see restore concept and overview Restoring Overview
Fifth, the following are some special scenario or features for backing up or restoring SQLServer2008 database
0. Restore or re-build system database
4. Compressed backup (Available in SQLServer 2008 Enterprise version and SQLServer 2008 R2 Standard version and all highest version)
and Backup Compressed
Last, some Best Practice you may need to consider.
The following items are just the best practice for regular case, but as requirement and resource are different, some may not fit for the special case or need to re-consider for sure.
1. Setup the database backup to full recovery mode except those system database and development environment.
2. Perform a full database backup per night off peak hours, differential backup at noon, transaction log backup per hour or more frequent.
3. Separate the system database backup and user database backup, the reason is the separation will isolate different database group backup and won’t impact each other if some errors occurred.
4. Perform Index rebuild maintenance per week when fragmentation is great than 40%, and transaction log backup should be stopped before the index re-build process and continue after (No means to backup those index rebuild logs, also the fragmentation between 10% and 40% should do index re-organize not re-build).
5. If compression backup is selected, try to use resources governance to allocate proper CPUs to the backup process.
6. Either leverage by checksum option before the backup or verify the backup file by trying to restore as “VERIFYYONLY” option after the backup completed. Build a standalone test environment to test the backups occasionally.
7. When accident occured, before restore the available backups(full, differential, translog), do a trailer log backup, also can leverage the feature called restore to the point in time or marked transaction. Please reference to A Quick Script for Point in Time Recovery – Back Up and Restore
8.Backup database to a fileshare, then back the share up to tape.
This mainly consider the restore time frame issue. Tape drives these days are fast enough that the vendors like to say DBAs should go straight to tape, and they’re technically right: tape backup & restore speed is not a bottleneck. However, there’s a limited number of drives available. When the clients needs a restore right away, the tape drives aren’t necessarily sitting idle. it can be a lag of hours if the tape device is busy. Disk backups, on the other hand, are always available, that’s why normally we kept 2-3 days database backup on the disk, rotate and delete the oldest backup per day.