SQL Server Backups

The Backup Database tool within the SQL Server Management Studio may be used to backup databases. Backups generated through this tool have an .abf extension. Right click on the name of a database in the Object Explorer and select Tasks -> Backup... to open the backup database dialogue box.

SQL Server Backup Dialogue

You may specify a recovery type, a timestamp for Backup set expiry, and a destination for the backup files. You may use the options tab (click on 'options' in the left hand window) to either append to an existing database backup file or overwrite backup files on the selected drive and media. Upon completion of the backup, you may choose to verify the backup for integrity. The options dialogue also allows users to maintain a log of the backup and use a tape drive for the backup. Click the 'OK' button to start the backup. Once the backup is complete, a pop-up box will appear to indicate operation status (success, error messages etc.).

The following command may be used to backup a database through a command executed on a Query panel:

BACKUP DATABASE Northwind TO DISK = "f:\backup\mydbase.bak"

SQL Server offers three recovery models - FULL, BULK_LOGGED, and SIMPLE. A new database's default recovery model is FULL. This method offers maximum flexibility. You may recover either the entire database or just a part of the database via the FULL recovery model. The full method uses a significant amount of transaction log space and is slightly expensive in terms of CPU usage. The BULK_LOGGED model is less flexible than the FULL model but also consumes smaller quantities of system resources. The SIMPLE recovery model is easiest to implement. This model is analogous to an incremental recovery; the SIMPLE model allows users to recover from the point of the previous backup. However, recovery is limited to when the database was last backed-up. The following command executed within a new query panel will obtain the current recovery model of the database:

SELECT dbpropertyex("database", "recovery")

The following command may be used to change the recovery model on the command line (rather than through the backup dialogue on the Management Studio GUI).

ALTER DATABASE database name SET RECOVERY {FULL | SIMPLE | BULK_LOGGED}

SQL Server backs-up database structure and metadata along with data and may be performed while users are still logged on to the server. You may backup to an explicitly specified physical device or to a logical device assigned to an actual physical devise.