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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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