Database Backup - Differences between Oracle, MSSQL, and MySQL

Oracle 10g
(refer http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch23.htm)


A. Full Backup
i)  NOARCHIVELOG mode - best for one-time database restore e.g. create testing/development database
ii) ARCHIVELOG mode - best for database recovery


B. Partial Backup
i)   backup all datafiles under ONE tablespace
ii)  backup SINGLE datafile
iii) backup a control file
> useful under ARCHIVELOG mode so that upon restore, changes will be applied based on archived redo log.


C. Incremental Backup
i)  differential incremental backup - changes in data blocks since most recent backup at the same level or lower
ii) cumulative incremental backup - changes in data blocks since most recent backup of the next lowest level


MSSQL 2005/2008/2012
Concepts of Recovery Model
i)   Simple - no support for log backup
ii)  Bulk-Logged - minimal logging for bulk operation. DMLs are still logged. Bulk operation after last backup cannot be recovered.
iii) Full - support point-in-time recovery
(refer http://msdn.microsoft.com/en-us/library/ms189275.aspx)


A. Full Backup - data backup (all online data files and transaction logs) at certain point of time.
                        - if there is any offline datafile, full backup operation will fail. administrator has to perform partial backup instead.
    (refer http://msdn.microsoft.com/en-us/library/ms175477.aspx)


B. Log Backup - changes in transaction log based on last full/differential/log backup (only available when recovery model is set to FULL or Bulk-Logged)
    (refer http://msdn.microsoft.com/en-us/library/ms191429.aspx)


C. File Backup - backup all the data in one or more online files or filegroups (a filegroup that contains 1 or more offline datafile will be considered as offline filegroup. therefore, it cannot be backed up)
    (refer http://msdn.microsoft.com/en-us/library/ms189860.aspx)


D. Partial Backup - useful when we want to exclude read-only filegroups
    (refer http://msdn.microsoft.com/en-us/library/ms191539.aspx)


E. Differential Backup - changes since latest full/partial backup (a.k.a. cumulative incremental backup)


MySQL 5.0/5.1/5.5
(refer http://dev.mysql.com/doc/refman/5.0/en/backup-types.html)


A. Full Backup 
    - complete database backup
    - Command: mysqldump (for MyISAM/Archive table, can use mysqlhotcopy)
        - does not include INFORMATION_SCHEMA
        - does not include PERFORMANCE_SCHEMA
        - does not include ndbinfo
        - does not include log/configuration file
(refer http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html and http://dev.mysql.com/doc/refman/5.5/en/mysqlhotcopy.html)


B. Incremental Backup 
    - MUST enable --log-bin on startup (or specify log_bin and expire_log_days in my.cnf)
    - flush bin log to file for database recovery later (similar to log backup)


C. Snapshot Backup - capability of file system. provided by third party (Veritas, LVM, ZFS)


Note: physical backup vs logical backup, online backup vs offline backup, import/export data and compressed backup are not covered in this article.


next post will discuss how to use database backups to perform recovery.