BACKUPS ARE A MECHANISM TO CREATE STANDBY COPIES OF ANY DATABASE.
THESE STANDBY COPIES ARE IN THE FORM OF files.
THE BACKUP FILES ARE OF FORMAT : bak,trn,diff
THESE FILES ARE USED TO RECOVERY / RESTORE THE DATABASE DATA & LOG FILES
INCASE OF ANY FAILURE.
BACKUPS ARE ONLINE OPERATIONS, ONLY COMMITTED LOG RECORDS AND DATA RECORDS ARE BACKED-UP.
Recovery Models ->
SIMPLE: Log truncates on checkpoint, Point-in-time recovery not possible
FULL: Point-in-time recovery supported ,Log Backups require
BULK-LOGGED: Reduce log space usage by minimally logging the bulk operations, Can recover to the end of any backup. Point-in-time recovery is not supported.
-- COMPLETE SYNTAX OF BACKUPS:
BACKUP database | log DBNAME
FILEGROUP | FILE = (<LIST>)
TO
DISK | TAPE | URL = location & EXTENSION {BAK / TRN}
WITH
DIFFERENTIAL -- To perform differential backup
, COMPRESSION -- To compress the backup file for less space
, COPY_ONLY -- a non base full backup for remaining differential, log backups
, PASSWORD = XXXX -- to secure the backup operation
, MEDIAPASSWORD = XXX -- to secure the backup file
, INIT -- To initialize the header information (DB Metadata)
, SKIP -- To skip / ignore the backup validation and expiry
, CHECKSUM -- To validate/verify backups by computing UNIQUE PARITY bits
, CONTINUE_AFTER_ERROR -- To continue the backup even though error occured @ validation
, RETAINDAYS = YYYY -- To set expiry date / time for backup file
, FORMAT -- To overwrite the backup file (if exists). DEFAULT: APPEND
, NORECOVERY -- To get the Online Database into a state for DB RECOVERY
, STATS -- To report the progress of backup.
Types of Backup:
Full Backup: it provides complete copy of database.
Backup Database DBNAME to disk=‘Backup-device’ With stats=,noinit,compression,checksum
Differential Backup : Differential backups are applicable for Complete Database, Individual File-group and Individual Data Files only. Not applicable for Log Files.
Backup Database DBNAME to disk=‘Backup-device’ With Differential
T-log backup :WHENEVER WE PERFORM LOG BACKUP, CHECKPOINT IS AUTO ISSUED AND ALL COMMITTED LOG RECORDS ARE AUTO TRUNCATED FROM LOG FILE.
Backup log DBNAME to disk= =‘Backup-device’ With stats
Copy-Only Backup : it doesn’t break the LSN chain.
Backup log DBNAME to disk= =‘Backup-device’ With COPY_ONLY
Mirror Backup :take same backup on multiple locations using mirror backup. we have to use FORMAT clause while taking mirror backup.
Backup Database DBNAME to disk= =‘Backup-device’
Mirror to Disk =‘Backup-device’ with FORMAT
Stripe Backup : we can stripe the backup to different disks using stripe backup.
Backup Database DBNAME to disk=‘Backup-device1’,
disk=‘Backup-device2’ with stats
Partial Backup: Takes backup of Read Write File-groups.
Backup database DBNAME READ_WRITE_FILEGROUPS to disk=‘Backup-device’
Partial Backup: Takes backup of Read Write File-groups.
Backup database DBNAME READ_WRITE_FILEGROUPS to disk=‘Backup-device’
Tail log backup : Tail log backup with NORECOVERY(Leave the database in restoring state) Option and with NO_TRUNCATE(when database is damaged).
Compressed Backup : Backup database With COMPRESSION clause.
FILEGROUP BACKUP: when we want to backup any specific Filegroup in database.
BACKUP DATABASE DBNAME FILEGROUP = fgname TO DISK = 'location'
BACKUP DATABASE dbname FILE = filename TO DISK = location
0 comments: