Backing Up and Restoring Data

Microsoft SQL Server offers several options for backing up data:

Full database backup

To make a full database backup, use the BACKUP DATABASE statement or the Backup Wizard.

Differential backup

After a full database backup, regularly back up just the changed data and index pages using the BACKUP DATABASE WITH DIFFERENTIAL statement or the Backup Wizard.

Transaction log backup

Transaction logs in Microsoft SQL Server are associated with individual databases. The transaction log fills until it is backed up or truncated. The default configuration of SQL Server 7.0 is that the transaction log grows automatically until it uses all available disk space or it meets its maximum configured size. When a transaction log gets too full, it can create an error and prevent further data modifications until it is backed up or truncated. Other databases are not affected. Transaction logs can be backed up using the BACKUP LOG statement or the Backup Wizard.

File or filegroup backup

SQL Server can back up files and filegroups. For more information, see SQL Server Books Online.

Backups can be performed while the database is in use, allowing backups to be made of systems that must run continually. The backup processing and internal data structures of SQL Server 7.0 have been improved so that backups maximize their rate of data transfer with minimal effect on transaction throughput.

Both Oracle and SQL Server require a specific format for log files. In SQL Server, these files, called backup devices, are created using SQL Server Enterprise Manager, the Transact-SQL sp_addumpdevice stored procedure, or the equivalent SQL-DMO command.

Although backups can be performed manually, it is recommended that you use SQL Server Enterprise Manager and/or the Database Maintenance Plan Wizard to schedule periodic backups, or backups based on database activity.

A database can be restored to a certain point in time by applying transaction log backups and/or differential backups to a full database backup (device). A database restore overwrites the data with the information contained in the backups. Restores can be performed using SQL Server Enterprise Manager, Transact-SQL (RESTORE DATABASE), or SQL-DMO.

Just as you can turn off the Oracle archiver to override automatic backups, in Microsoft SQL Server, members of the db_owner fixed database role can force the transaction log to erase its contents every time a checkpoint occurs. This can be accomplished by using SQL Server Enterprise Manager (truncate log on checkpoint), Transact-SQL (sp_dboption stored procedure), or SQL-DMO.