Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 14, 2002

SQL Server 2000 Administration in 15 Minutes a Week: Database Backups - Page 2

By Michael Aubert

Database Backup Types

SQL Server has three main backup types: Full, Differential, and Log. In addition to the main three that backup the whole database, there are also a few additional types of backups that can be used to backup a single file or a filegroup. 

Full Backups - a full backup copies all the extents from the database. If you need to restore your database and are using full backups, you only need to apply the last full backup. However, full backups are the slowest of all the backup types.

Differential Backups - a differential backup only backs up the extents that have changed from the last full backup. If you need to restore your database and are using differential backups, you will need to apply the last full backup and then the last differential backup you have made. Differential backups are faster to create, but they take longer to restore than a full backup because you have to apply the full backup and then the differential backup.

Log Backups - a log backup is used to backup the transaction log from the last full or transaction log backup. You may or may not be able to make log backups -- it depends on the  recovery model you are using. If you need to restore your database and you are using full and log backups, you would need to restore the last full backup and all (in order) the transaction log backups.

One thing to note about backups is that they are done with the database online. This is called a "fuzzy backup" because the backup is not from a single point in time. The backup copies extents from the database and if any modifications are made, the backup just continues copying. To keep consistency, full and differential backups capture the portion of the log file from the point the backup started to the end of the backup.

SQL Server can backup to a file on your hard drive, a file on a network drive, a tape drive, or a named pipe.

Page 3: Database Recovery Models

 » See All Articles by Columnist Michael Aubert

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM