Database Backups- The Crib Sheet
Contents
General Issues
Database Backup Strategies
Plan this up-front. Any enterprise using databases must maintain a written plan for backing up all data. This plan has to identify the various classes of data held, (e.g. financial, corporate, departmental, personal), the risk of destruction, its value to the enterprise and the general strategy for minimising the damage or cost that would follow the loss of part, or all, of that data. An agreement must exist for all production system, which defines the maximum tolerable loss of data and the database downtime. The strategy must be tested against various eventualities to verify that a full recovery of the database can be made in all circumstances. The strategy must be available for inspection by external auditors, insurers or assessors.
Why back-up?
Backup is essential because of Disk failure, malicious damage, stupidity, fraud-detection, or database corruption, It is also necessary for reversing one or more transactions, copying a database, or archiving a system at a point of time for audit purposes.
What is a backup?
A backup consists of a number of files or 'devices' that together restore the system and user databases to exactly the state of the database system at a particular time. Backups generally consist of a number of full backups and intermediate Log backups. In order to restore a database, the last full backup prior to the point you want to restore to needs to be applied, followed by the log backups prior to that point in time, but subsequent to the full backup. In order to back up a database system, all contributing system databases need to be backed up.
There was a time when databases backups had to be undertaken when the database was offline. This is because any change in the data during the backup process can leave the backup corrupted, as it must necessarily represent the database at a point in time. By the time a backup is completed, an online database would have changed. Now, almost all commercial databases use online backups. This involves writing all completed transactions to disk before copying the database. No transactions after this point are included in the backup. Backing up a database by merely copying its files still must be done with the database offline.The apparent complexity of a database backup is mostly due to the requirement for online backup, and the occasional need to restore to a particular point in time
When do backups?
This needs to be a conscious decision enshrined in the Service-level agreement for each application. How much time do you want to spend restoring your system? How much data can you afford to lose? How often does the data change in the database? Common practice is that transaction logs need to be backed up ''off site'' incrementally every few minutes. Complete backups, generally speaking, should be done daily
Where should backups be placed?
Database backups should be ultimately held ''off site'' if they are ''Financial'', or essential to the enterprise. Nowadays this merely means renting internet secure FTP storage. The initial backup is generally done to ''local'' disk storage.
What needs backing up?
It is not only the user database that must be backed up. This is also true of the system databases that contribute to the functioning of the server and related servers. The scripts for all database objects, such as stored procedures and triggers, should be held separately in source-control, with a permanent record of all alterations to the objects of the production system.
Who should perform backups?
It shouldn''t matter who actually performs the backup as the procedures will be well documented, and will normally be entirely automated.
The DBA nominated by the production manager, or equivalent, needs to have overall responsibility under the terms of the SLA for all ''live'' or ''production'' databases. The DBA needs to ensure that all production systems have an SLA, and that the category of data has been correctly identified and that the SLA is appropriate. He must then devise a backup strategy that meets these requirements. Development systems are done on separate terms and may be supervised by a different chain of responsibility. All developers should ensure that DEV databases are recorded and scheduled for backup. Ad-hoc full backups are sometimes necessary after a non-logged operation, and the DBA must provide all necessary facilities and access rights for the operators who perform such operations, so they can subsequently perform the backup.Backup media and media rotation
Traditionally, databases used to be ''dumped'' onto tape. Tape is slow and not very reliable. Backups are now best written to local disk firstly, and then copied to its ultimate destination. USB drives, MO drives, network drives, NAS or SAN are fine for this, but for databases that are critical to the functioning of the enterprise, a copy must be stored offsite. It is unwise to delete all but the most recent backup, as the integrity of a particular backup cannot be assumed, unless a test restore has been undertaken, followed by a DBCC check. The period for which backups are stored is a matter determined by the backup strategy.
SQL Server issues
Types of Backup
There are several ways one can backup a database. To do an online backup, one would normally choose a combination of complete backup and incremental backup, for which the database must be set to either full or bulk-logged recovery model. However the choice of backup method depends very much on the type of database. Where a database is large and relatively unchanging, then other methods, such as filegroup backups would be more manageable. Generally, system databases must rely only on complete backups. Incremental backups are unique in that they back up the latest transactions of the transaction log, whereas all other types back up the data itself.
- Complete or Full backups
- Full Backups, or Complete backups, include all the data, system tables and database objects of the database. All backup sets must start with a Full Backup A complete database backup creates a self-sufficient, stand-alone image of the entire database and log at a point in time, and may be restored to any database on any server A complete backup may be restored for databases regardless of their recovery model Complete database backup should be done regularly for all production databases. System databases (including master and MSDB) should have a complete backup if there are any changes performed to the server operating environment such as creating or removing databases, creating and modifying DTS packages or scheduled jobs, configuring security, adding and removing linked servers etc.
- Incremental (transaction log) backups
- An incremental backup- commonly known as a transaction log backup (the term we use from here in) - stores a backup of all the modifications over a period of time. In other words, a transaction log backup backs up all of the transactions processed by the server for this database since either the previous transaction log backup, or the first complete database backup - whichever is the sooner. This backup may then be used to apply the backed-up changes, to restore the database to a point in time. Each time the transaction log is backed up all of the committed transactions in the log are removed and written to the backup media. Because of this incremental process, transaction logs are not cumulative. Consider the following backup regime: 10:00 - full backup 10:15 - incremental backup A 10:30 - incremental backup B 10:45 - incremental backup C ..etc.. To restore the database as at 10:45, first restore the full backup at 10:00 and then apply all of the incremental backups (A, B and C). Each incremental backup will only restore the transactions over a particular time period (B, for example, stores all transactions between 10.15 and 10.30). The time between incremental backups represents the processing time you are prepared to 'lose' in the restored database if a complete failure occurs. A non-logged operation done by switching to simple recovery model will mean that the transaction log is out of sync with the database and so a database backup must then be performed. Transaction log backups are only effective if the database is in full or ''bulk-logged'' recovery mode. The Master and MSDB databases are in simple recovery mode and cannot provide effective incremental backups. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers. Transaction log backups must be restored in the correct sequence from oldest to newest.
- Differential backups
- Differential backups are done in conjunction with a full backup. Whereas transaction log backups can only be used to restore a database if all incremental backups are applied in sequence, differential backups record all the changes (modified extents) since the last full backup and are therefore cumulative. Applying the previous backup regime to diff backups, to restore the database as at 10:45, you would first restore the full backup at 10:00 and then apply differential backup C only. Differential backups work best for databases that are large and are modified infrequently, such as ''data warehouses''. Usually, this type of database can be restored more quickly from a complete backup followed by a differential backup, than from a series of incremental backups. Differential backups cannot be used to restore to a point-in-time as they do not use the transaction log. However, one can apply transaction logs after a differential log backup to make a `point in time` recovery. Differential backups cannot be used for the master database since, when restoring from a differential backup, the first step is to apply the full backup without recovery, which is not allowed for the master database.
- File and filegroup backups
- Rather than back up the entire database, you can back up individual files and filegroups (a logical grouping of database files) within a database. A file/filegroup backup does not back up the transaction log portion of the database, so cannot be used as the first step to recover a database unless the transaction log is also backed up. A file/filegroup backup may only be restored to the database it was backed up from, and all transaction log backups, including the ''tail'' (log records that have not been backed up), for the database should be restored in their entirety in order to leave it in a consistent state. Because of this, point-in-time recovery cannot be done when restoring file/filegroup backups. /dd>
- File differential
- File differential backups can be combined with file/filegroup backups to back up only the modified extents within an SQL Server database file or filegroup. They are most useful for large, relatively static, databases and are often quicker to restore in cases where only certain file/filegroups are lost. File/filegroup differential backups are cumulative, so in order to restore the files in question you just need the latest differential backup and any transaction log backups performed after the latest file/filegroup differential.
Performing Backups
The following describes a typical backup routine when using T-SQL or the Enterprise manager console in SQL 2000. Options have been tweaked slightly in SQL 2005 Management Studio, but the same basic principles apply.
| Backup Type | TSQL | console |
|---|---|---|
| Full Backup | BACKUP DATABASE {databasename} TO {device}. | 1. Expand the Databases folder, right click on the database in question and select All Tasks | Backup Database. 2. In the Dialog box, provide a name for the backup in the Name text box. 3. Leave the 'Database - complete' option selected. 4. Select either the 'Overwrite existing media' option to overwrite the current backup to existing file or device. Or the ''Append'' to Media'' option to add it to the end. 5. To select a destination for the backup, click the Add button. 6. In the next dialog box, Select an existing file or enter a new file name. Click OK. 7. Click the Options tab (page in SQL 2005). Select the desired options, then either click the OK button to start performing the backup, or check the Schedule check box to schedule this operation for periodic execution. |
| Transaction log (Incremental) Backup | BACKUP LOG {databasename} TO {device}. | As above, except that, to perform a transaction log backup, select the Transaction log option in step 3. It is unwise to select 'Overwrite Existing Media' radio-button. In the Options tab, 'remove inactive entries from transaction log' should be set. The Back up the tail of the Log option should not be set, unless preparing for a restore. |
| Differential backup | BACKUP DATABASE {databasename}TO {device}WITH DIFFERENTIAL | As above, except that, to perform a differential backup, select the database-differential radio button. |
| Filegroup Backup | BACKUP DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} TO {device} | As above, except that, to perform a filegroup backup, select the File and filegroup radio button. Click the ellipses button next to this option. In the Specify Filegroups and Files dialog box, select the files/filegroups that you would like to back up, then click OK |
| Filegroup Differential backup | BACKUP DATABASE {databasename} FILEGROUP = {filegroup}TO {device} WITH DIFFERENTIAL |
System database backups
To restore a database server, the system databases will need a backup regime that includes at least the Master database and MSDB
- The Master database
- Must be backed up after creating, altering or dropping a database; altering a data or log file; changing logins; changing configuration options; altering linked servers, remote servers, etc.
- MSDB
- Must be backed-up if the SQL Server Agent is being used to preserve Job Schedule and job history information
- Distribution database
- Needs to be backed up if replication has been configured and the server is a Distributor. There should be a backup after snapshots and, if transactional replication is in place, there should be regular log backups
- Publication database
- This needs to be backed up if replication has been configured and the server is a Distributor, and any a replication setting is changed
- Subscription database
- Needs to be backed up if replication has been configured and the server is a subscriber
- Model
- The model database needs to be backed up only after changes have been made to it.
Recovery models
A SQL Server Database can be set to one of three recovery models. This effects the way that transaction logging is done. The recovery model of the database determines what is possible by way of backup
- Simple
- If a database is using the simple recovery model, then the database must be restored from the last full backup, because the contents of the database transaction log are truncated each time a checkpoint is issued for the database.
- Full
- If a database is using the full recovery model, then the database can be restored from the last full backup, followed by the incremental transaction log backups
- 'Bulk-logged'
- A database with 'bulk-logged' recovery will function much like a 'full' one except that only the effect of bulk operations are recorded in the backups, rather than the transactions involved. These bulk operations include BCP, certain Data Transformation Services (DTS) operations, image and text manipulations, and SELECT INTO. By using a 'bulk-logged' recovery model, the bulk operations are unlogged and therefore much quicker. Point-in-time recovery is not possible if one of these bulk operations have taken place because if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations, and this applies to all changes up to the point that the log was backed up.
| Set Recovery model | TSQL | console |
|---|---|---|
| Full | ALTER DATABASE {databasename}SET RECOVERY FULL GO | Open the 'Databases' folder. Once the database folder is expanded, right click on the database and select the 'Properties' option. The 'Database Properties' window will open. Click on the 'Options' tab and the recovery model will be listed in the middle of the screen. Click on the drop down box to select the needed recovery model. On the bottom of the screen click 'OK' to save the Recovery Model |
| Simple | ALTER DATABASE {databasename}SET RECOVERY SIMPLE GO | |
| Bulk Logged | ALTER DATABASE {databasename}SET RECOVERY BULK_LOGGED GO |
Replicated Databases
Replicated databases require different strategies for backup which are beyond the scope of this article. Please refer to Backing up and Restoring Replicated Databases in BOL for details http://msdn2.microsoft.com/en-us/library/ms151152.aspx
Striped backups
In order to reduce the time taken to perform a backup, SQL Server can write in parallel to up to 32 different devices, grouped together as a ''Media Set''. (a media family is a set of backup units grouped together to make one logical device). To do the backups, the devices must be defined within a backup device, and must all be of the same type. This is most effective with slow devices such as Tape/DAT drives
Continuation media
If you have to backup directly to tape (rather than to file-then-tape) and the tape fills up during a backup operation, SQL Server prompts for the next tape (if using the Transact-SQL command, a message is logged to the SQL Server error log to mount the next tape and a retry attempt is made roughly every five minutes to see if a new tape was mounted; if using Enterprise Manager, a dialog box appears.). With disk backups, the backup operation is aborted if the disk drive becomes full.
Standby servers
Where necessary, a backup strategy will involve keeping standby servers ready and waiting, in a state where they can take over the functioning of the primary server without loss of data. The technology to do this usually involves applying the full backups to the spare server and applying the transaction log backups to the standby when the backups are done. Wherever a non-logged operation is performed, the servers need to be resynchronized. You cannot backup or restore a mirror database whilst mirroring is active.
- Hot Standby
- This only applies to SQL Server 2005, where database mirroring is introduced. A Hot Standby is a server that can switch in automatically on failure of the primary server, and is automatically kept up-to-date (e.g. by database mirroring).
- Warm Standby
- This represents a server that is automatically updated but has to be manually switched to replace the primary server. This is usually done by log shipping, which normally means creating a new database by restoring a full backup, and then automatically applying the incremental backups to it in order to synchronise it. Alternatively, where the databases are of a reasonable size, an automated synchronisation via a third-party synchronisation tool provides a useful and reliable alternative.
- Cold Standby
- Cold Standby servers are manually updated and have to be manually switched to replace the primary server.
Backup History
The history of every SQL Server backup is written to the MSDB database. This can be accessed easily via SQL
- backupfile
- Contains one row for each data or log file that is backed up
- backupmediafamily
- Contains one row for each media family
- backupmediaset
- Contains one row for each backup media set
- backupset
- Contains a row for each backup set
- backupfilegroup
- Contains one row for each filegroup in a database at the time of backup
- logmarkhistory
- Contains one row for each marked transaction that has been committed
- suspect_pages
- Contains one row per page that failed with an 824 error (with a limit of 1,000 rows)
- sysopentapes
- Contains one row for each currently open tape device
How Backups should be done
SQL Server Backups are generally automated. The T-SQL for a scheduled backup is placed in the SQL Server Scheduler. However, there are circumstances where a particular backup needs to be started manually. (e.g. after a non-logged bulk load). In general, the backups should be administered via the console (either the Enterprise Manager or SSMS according to version) until the regime is settled, tested, and agreed. At that point it can, if required, be enshrined in a stored procedure.
When the backup is done via the console, then one can use the automated recovery process. With automated recovery, the process becomes much easier to do, with no issues as to which backup to apply when, which command flags to use when, etc.Ideally, the backup procedure will be completely automated by the DBA, and tested in a variety of circumstances by doing various database restores in a number of different circumstances.
To schedule a backup using the Maintenance Plan Wizard
If the concepts explained in this crib sheet are well-understood, then the best way of creating a maintenance plan is to use the Maintenance Plan wizard (Maintenance Plans subsystem Wizard in 2005). However, it will not suit all circumstances and is best used as a guide. It uses sqlmaint instead of native backup commands, and cannot do differential backups.
For Enterprise Manager in Windows 2000, Right-click the database and select All Tasks, then select Maintenance PlanClick
At the first dialog box 'Select Databases dialog box', verify that database(s) you want is/are selected by checking them in the list.
Click
Select what you wish. Modify the schedule by clicking the Change... button.
Click
Select the options available to perform a database integrity check.
Click the
Select the options for your Backup plan
Click the
Select the options that suit your backup plan
Click
Select the options that suit your backup plan
Click
Select the options that suit your backup plan
Click
If selected, a report be generated and should be read to spot any failures that might have occurred.
click
Select to log history records for the execution of this maintenance plan to help troubleshooting.
Click the
Enter a name for this maintenance plan.
Click the Finish button to create the maintenance plan.
Third Party Backup tools
The most common problem of the SQL Server backup is that it is written out in an uncompressed form, and can take a large amount of space. This was a good idea in the era of magnetic tapes, when the compression was done in hardware on the device, but it is now an obvious omission. Several third-party products have stepped into the gap to produce products with various other added features such as object-level, and table-level recovery, spreading a backup by 'striping' across multiple files within a directory, log-shipping, and enterprise-wide backup.
- Red Gate SQL Backup
- Red Gate SQL Backup is able to choose from four different compression levels according to the desired trade-off between size of the backup and the speed of the backup. It is able to split backups and encrypt them using different levels of encryption. You can specify the maximum size of the data blocks to be used when SQL Backup writes data to network shares. It includes a Log Shipping wizard to implement log shipping between two servers
- Quest Litespeed
- Litespeed is one of the oldest third-party backup solutions with a number of different variations. It has, in the past, always majored on its performance and the extent of the compression it can achieve, though independent tests by The Tolly Group in July 2006 to evaluate the disk-to-disk backup performance of SQL Backup Pro 4 and Litespeed show SQL Backup to be faster. Recent 'enterprise' versions have table-level recovery.
- Also...
- Netvault Backup
- Ultrastor
- Bakbone Netvault
- Idera SQLsafe
- Computer Associates BrightStor Arcserve Backup
- IBM Tivoli Storage Manager
- EMC Dantz Retrospect
- EMC Legato Networker
- Hp OpenView Data Protector
- Veritas NetBackup (Symantec)
- Veritas Backup Exec (Symantec)
No comments:
Post a Comment