BACKUP: A MECHANISM TO CREATE STANDBY COPY OF THE DATABASE IN A FILE FORMAT. These are called “BACKUP FILES”. BAK or TRN files.
LEVELS OF BACKUPS & RESTORES:
1. COMPLETE DATABASE LEVEL BACKUPS
2. FILEGROUP LEVEL BACKUPS
3. FILE LEVEL BACKUPS
TYPES OF BACKUPS:
1. FULL BACKUP : ALL PAGES (DATA PAGES & INDEX PAGES, LOG PAGES) IN DATABASE ARE INVOLVED IN BACKUP
2. DIFFERENTIAL BACKUP : PAGES THAT CONTAINS CHANGES SINCE LATEST FULL BACKUP ARE INVOLVED
3. LOG BACKUP : ALL PAGES FROM LOG FILE ARE INVOLVED IN BACKUP
COPY ONLY BACKUP: A SPECIAL TYPE OF FULL BACKUP
THESE BACKUPS DOES NOT BE A BASE A FOR REMAINING DIFFERENTIAL AND LOG BACKUPS. ISOLATED BACKUP.
USED FOR REPLICATON & DATABASE MIGRATIONS [TO MOVE DATABASE FROM ONE SQL SERVER INSTANCE TO ANOTHER]
PARTIAL BACKUPS: SUCH BACKUPS WHICH ARE PERFORMED ON READ WRITE FILEGROUPS OF THE DATABASE.
MEANS, THESE PARTIAL BACKUPS DO NOT BACKUP READ ONLY FILEGROUPS OF THE DATABASE.
MIRRORED BACKUPS: BACKUPS TAKEN TO MULTIPLE LOCATIONS AT THE SAME TIME. ONE LOCATION IS MIRROR / DUPLICATE FOR ANOTHER.
SPLIT BACKUPS: BACKUPS TAKEN TO MULTIPLE LOCATIONS AT THE SAME TIME. ONE LOCATION IS CONTINUOUS PART TO ANOTHER.
HOT BACKUPS: SUCH BACKUPS THAT ARE PERFORMED ON THE DATABASE WHICH IS ONLINE, LIVE AND IN USE. THERE CAN BE USERS CONNECTED.
COLD BACKUPS: SUCH BACKUPS THAT ARE PERFORMED WHEN THE DATABASE IS OFFLINE. USED FOR DB MIGRATIONS FROM ONE SERVER TO ANOTHER.
BACKUP OPTIONS:
MEDIA OPTIONS:
FORMAT : TO OVERWRITE THE BACKUP FILE, IF EXIST
NOFORMAT : TO APPEND CURRENT BACKUP TO EXISTING BACKUP FILE
BACKUP VERIFICATION : TO VERIFY AND ENSURE THE BACKUP FILE IS VALID
CHECKSUM : TO AUTO DETECT AND CORRECT THE ERRORS IN BACKUP FILE, IF ANY
CONTINUE_ON_ERROR : TO CONTINUE THE BACKUP PROCESS EVEN THOUGH THERE ARE ERRORS
EX: DATABASE CONTAINS 4 FILEGROUPS, 10 FILES.
3RD FILEGROUP IS CORRUPTED. STILL, THE REMAINIG FILEGROUPS & FILES ARE BACKUP.
BACKUP OPTIONS:
EXPIRY : TO SPECIFY THE BACKUP FILE RETENTION TIME
COMPRESSION : TO COMPRESS OR ZIP THE BACKUP FILE CONTENTS – TO SAVE DISK SPACE
TRUNCATION : TO CLEAR / REMOVE THE CONTENTS FROM VLFs