How To Take A Backup Of Your MSSQL Database

How To Take A Backup Of Your Database

Create Database [BANK_DATABASE]                --create database

USE [BANK_DATABASE]

CREATE TABLE tblACCOUNTS 

(AccID int, 

AccStatus bit, 

AccBal float)                                                              --create table

INSERT INTO tblAccounts 

VALUES (10001, 797009, 1), 

(10002, 343733, 1),

(10003, 0, 1)               --insert values in table

 

CREATE TABLE tblINSURANCE 

(InsrID int,

InsrStatus bit, 

InsrBal float)                                                  --create 2nd table

 

INSERT INTO tblInsurance 

VALUES (10001, 457444, 1), 

(10002, 347333, 1),

(10003, 440, 1)           --insert values in it

--Check values in Tables 

select * from tblACCOUNTS

select * from tblInsurance

Types Of Backup 

BACKUP DATABASE [BANK_DATABASE] 

TO DISK = 'E:\FullBackup.BAK' with FORMAT                  --take full backup

 

--insert another values after full backup

INSERT INTO tblINSURANCE DEFAULT VALUES;

INSERT INTO tblACCOUNTS DEFAULT VALUES

INSERT INTO tblAccounts 

VALUES (10004, 797009, 1)                                                                                                                    

How to take Differential Backup 

BACKUP DATABASE [BANK_DATABASE] 

TO DISK = 'E:\Diff_1.BAK' with FORMAT, DIFFERENTIAL

-- insert another values after differential backup

INSERT INTO tblInsurance VALUES (10004,457444, 1)

 How to take another Differential Backup 

BACKUP DATABASE [BANK_DATABASE] 

TO DISK = 'E:\Diff_2.BAK' with FORMAT, DIFFERENTIAL

 -- insert another values after differential backup

INSERT INTO tblInsurance VALUES (10005,457444, 1)

INSERT INTO tblAccounts VALUES (10005, 797009, 1)

 How to take Log Backup 

BACKUP LOG [BANK_DATABASE] 

TO DISK = 'E:\LogBackup.trn' with FORMAT

 

 How to take copy-only Backup 

--does not break backup chain

BACKUP DATABASE [BANK_DATABASE] 

TO DISK = 'E:\Backup.BAK' with FORMAT, COPY_ONLY

 How to take Mirror Backup 

 BACKUP DATABASE [BANK_DATABASE]

 TO DISK = 'E:\LearningBackupClass\BANK_DATABASE_BACKUP_COPY1.bak'

 MIRROR

 TO DISK = 'D:\LearningBackupClass\BANK_DATABASE_BACKUP_COPY2.bak' WITH FORMAT

 How to take Spilit Backup 

BACKUP DATABASE [BANK_DATABASE]                                      --spilit backup

 TO DISK = 'E:\BANK_DATABASE_BACKUP_PART241.bak',

 DISK = 'E:\BANK_DATABASE_BACKUP_PART242.bak' WITH FORMAT

 

--file group--

BACKUP DATABASE [BANK_DATABASE] read_write_filegroups TO DISK = 'E:\pBackups.BAK'   --