Steps to Take Backup from GUI in SQL Server
ITEM #1: HOW TO PERFORM A DATABASE LEVEL FULL BACKUP ?
FROM SSMS >> DATABASES >> RIGHT CLICK DATABASE >>> TASKS >> BACKUP >> SELECT TYPE : FULL,
ADD DATABASE BACKUP FILE LOCATION AND INCLUDE EXTENSTION (bak) >> OK
ITEM #2: HOW TO PERFORM A DIFFERENTIAL BACKUP ?
FROM SSMS >> DATABASES >> RIGHT CLICK DATABASE >>> TASKS >> BACKUP >> SELECT TYPE : DIFFERENTIAL
ADD DATABASE BACKUP FILE LOCATION AND INCLUDE EXTENSTION (bak) >> OK
ITEM #3: WHAT PRECAUTIONS YOU CONSIDER WHEN PERFORMING ROUTINE DIFFERENTIAL BACKUPS ?
WHILE PERFORMING BACKUP, IN “MEDIA OPTIONS” SCREEN >> USE “OVERWRITE”.
ITEM #4: HOW TO PERFORM COPY ONLY BACKUP ?
FROM SSMS >> DATABASES >> RIGHT CLICK DATABASE >>> TASKS >> BACKUP >> SELECT TYPE : FULL, CHECK @ COPYONLY
ADD DATABASE BACKUP FILE LOCATION AND INCLUDE EXTENSTION (bak) >> OK.
ITEM #5: HOW TO VERIFY A BACKUP ?
WHILE PERFORMING BACKUP, IN “MEDIA OPTIONS” SCREEN >> USE “VERIFY BACKUP”.
ITEM #6: HOW TO ENSURE AUTOMATED BACKUP REPAIR ?
WHILE PERFORMING BACKUP, IN “MEDIA OPTIONS” SCREEN >> USE “CHECKSUM”.
ITEM #7: HOW TO PERFORM COMPRESSED BACKUPS ?
WHILE PERFORMING BACKUP, IN “BACKUP OPTIONS” SCREEN >> USE “COMPRESSION”.
ITEM #8: HOW TO TAKE BACKUPS WITH A SPECIFIC EXPIRY DATE ?
WHILE PERFORMING BACKUP, IN “BACKUP OPTIONS” SCREEN >> USE “EXPRIRY (RETENSION) IN DAYS or DATE”.
ITEM #9 : HOW TO PERFORM LOG BACKUPS?
FROM SSMS >> DATABASES >> RIGHT CLICK DATABASE >>> TASKS >> BACKUP >> SELECT TYPE : LOG
ADD DATABASE BACKUP FILE LOCATION AND INCLUDE EXTENSTION (trn) >> OK
ITEM #10: HOW TO TRUNCATE LOG FILES?
LOG FILES ARE AUTO TRUNCATED WHENEVER WE PERFORM A DATABASE LEVEL LOG BACKUP
TRUNCATION MEANS TO ERASE or RMEOVE THE COMITTED LOG PAGES FROM DATABASE LOG FILE.
THIS WAY, LOG FILE CAN BE FREED FOR NEW OPERATIONS.
HENCE WE NEED TO PLAN FOR ROUTINE LOG BACKUPS.
ITEM #11: HOW TO PERFORM ROUTINE DATABASE BACKUPS AND LOG BACKUPS?
ITEM #12: HOW TO PERFORM MULTI DATABASE BACKUPS?
ITEM #13: HOW TO USE SSIS (SQL SERVER INTEGRATION SERVICES) TO PERFORM BACKUPS?
SOLUTION: USING MAINTENANCE PLANS
STEP 1: RIGHT CLICK SQL SERVER AGENT >> START
STEP 2: MANAGEMENT FOLDER IIN SQL SERVER >>> MAINTENANCE >> RIGHT CLICK >> NEW MAINTENANCE PLAN
WE SEE TWO ADDITIONAL WINDOWS. ONE IS FOR TASKS AND OTHER IS FOR SSIS PACKAGE DESIGN (DESIGNER WINDOW)
STEP 3: DRAG n DROP “BACKUP TASK” > RIGHT CLICK > EDIT >> SELECT ALL DATABASES, PROVIDE LOCATIONS, EXPIRY
OK
STEP 4: SCHEDULE THE SUB PLAN. SAVE. THIS AUTOMATICALLY CREATES A “JOB”.
STEP 5: RIGHT CLICK JOB >> START JOB AT STEP. THIS EXECUTES THE JOB.
ITEM #14: HOW TO ENSURE ROUTINE MAINTENANCE?
SOLUTION: USING MAINTENANCE PLANS
RIGHT CLICK EXISTING (ABOVE) MAINTENANCE PLAN >> MODIFY >> ADD SUB PLAN >> ADD BELOW TASKS:
a. MAINTEANCE CLEANUP >> EDIT >> SPECIFY BACKUP FOLDERS LOCATION, TIME FRAME > OK
b. HISTORY CLEANUP >> EDIT >> SPECIFY TIME FRAME >> OK
ITEM #15: WHERE ARE THE BACKUPS GETTING AUDITTED?
IN MSDB.
EXPAND DATABASES FROM SSMS >> SYSTEM DATABASES >> MSDB >> SYSTEM TABLES >> WE SEE A SET OF FIVE SYSTEM TABLES:
BACKUPSET
BACKUPFILE
BACKUPFILEGROUP
BACKUPMEDIAFAMILY
BACKUPMEDIASET