SQL DBA: Backups

Description:

• Copy of the database that is used to recover the data after the system failure.
• Backups are used as safeguards for databases because data may be lost due to Media failures, user errors, Hardware failures, natural disasters, etc. With good backups, we can recover the database from failure.
• Takes the data and meta data into a separate file
• Online process

Types of backups:

1. Full Backup
2. Differential Backup
3. Transaction Log Backup
4. Tail Log Backup
5. File Backup
6. Filegroup Backup
7. Partial Backup
8. Split Backup
9. Mirror Backup
10. Copy only Backup
11. Compression Backup

 

1)  Full Backup:

• Captures all the data that is stored in the database. Backs up the entire database.
• It is the base for other backups.
• It takes a long time, depending on the size of the database.
• This allows the simplest form of database restoration since all the contents are contained in one backup.

 

Sql query:

BACKUP DATABASE database_name TO DISK=‘D:\Backupdrive\databasename_Full.bak’

Syntax:

BACKUP DATABASE prod TO DISK=‘E:\Backups\prod_full.bak’

 

 

2) Differential Backup:

• Differential Backup captures the modified and new extents since the Last Full Backup.
• It uses less space and saves the storage space and time it takes for a backup.
• Example:

  Sun          Fullbackup

           Mon       Differential Backup   [Sun-Mon]

           Tue         Differential Backup    [Sun-Tues]

 

Sql query:

BACKUP DATABASE database_name TO DISK=‘D:\Backupdrive\databasename_Diff.bak’ with differential

Syntax:

BACKUP DATABASE prod TO DISK=’E:\Backups\prod_diff.bak’ with differential

 

3) Transaction Log Backup:

• Captures the active Transactions and truncates the inactive transactions from the Transaction Log.
• It can be used to recover the data up to the point of failure or to restore the database to a specific point in time.
• It is implemented in log shipping, where log backups are scheduled periodically.
• This Backup type is possible only with the full or Bulk-Logged Recovery model.

 

Sql query:

BACKUP LOG database_name TO DISK=‘D:\Backupdrive\databasename_Tlog.trn’

Syntax:

BACKUP LOG prod TO DISK=’E:\Backups\prod_Tlog.trn’

 

 

4) Tail Log Backup:

• The Tail-Log Backup is the last Backup that is used to recover the database to the point of failure.
• Captures the tail of the log in the event that the database is offline, damaged, or inaccessible if the transaction log is accessible and undamaged.
• Supports only Full or Bulk-Logged Recovery models.

 

Sql query:

1. If the database is online:

BACKUP LOG database_name TO

DISK=‘D:\Backupdrive\databasename_Tail.trn’ with norecovery

2. If the database is offline, does not start, or is damaged:

BACKUP LOG database_name TO

DISK=‘D:\Backupdrive\databasename_Tail.trn’

with CONTINUE_AFTER_ERROR/NO_TRUNCATE

 

Syntax:(online mode)

BACKUP LOG prod TO

DISK=’E:\Backups\prod_Tail.trn’ with norecovery

 

 

5) File/File Group Backup:

• Captures only individual Files or Filegroups instead of taking the whole database into Backup. This is the alternative strategy to Full Backup for large databases.

 

Syntax for File Backup:

BACKUP DATABASE database_name FILE=‘File1_name’ TO

DISK=‘D:\Backupdrive\databasename_File1_name.FIL’

Syntax for Filegroup Backup:

BACKUP DATABASE database_name FILEGROUP=‘Filegroup_name’

 TO DISK=‘D:\Backupdrive\databasename_Fg_name.FLG’

 

 

6) Partial Backup:

• Introduced in SQL Server 2005, it processes the backup of read-write file groups in a database.
• This backup is a good option if you have read-only filegroups in the database and do not want to backup the entire database all the time.

 

Sql query:

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS

TO DISK=‘D:\Backupdrive\databasename_Partal.BAK’

 

Syntax:

BACKUP DATABASE prod READ_WRITE_FILEGROUPS

TO DISK=’E:\Backups\prod_Partial.BAK’

 

 

7) Split Backup:

• It splits the backup file into smaller chunks in multiple files, each being a separate file.

Sql query:

BACKUP DATABASE database_name TO

DISK=‘D:\Backupdrive\databasename_Full1.bak’,

DISK=‘E:\Backupdrive\databasename_Full2.bak’,

DISK=‘F:\Backupdrive\databasename_Full3.bak’

Syntax:

BACKUP DATABASE prod TO

DISK=’E:\Backups\prod_Full1.bak’,

DISK=’E:\Backups\prod_Full2.bak’,

DISK=’E:\Backups\prod_Full3.bak’

 

 

8) Copy-Only Backup:

• Copy-only backup was introduced in SQL Server 2005.
• Copy-only backups will not affect the overall Backup and Restore procedures.
• It cannot affect the Log Sequence Number.
• It supports full, bulk-logged, and Simple Recovery Models.

Example:

                 Sun Full   8pm

                 Mon  Diff    8pm

                 Tue    Diff    8pm

                 Wed   Full   2pm   (Ad-hoc) with copy_only

                  Wed   Diff   8pm

Sql query:

BACKUP DATABASE database_name

TO DISK=‘D:\Backupdrive\databasename_Full_copy.bak’

WITH COPY_ONLY

Syntax:

BACKUP DATABASE prod

TO DISK=’E:\Backups\prod_Full_copy.bak’

WITH COPY_ONLY

 

 

9) Mirror Backup:

• Another copy of the backup
• Can have up to three Mirror copies
• This feature is available in SQL Server 2005 Enterprise Edition and later versions.

 

Sql query:

BACKUP DATABASE database_name

TO DISK=‘D:\Backupdrive\databasename_Full.bak’

MIRROR TO DISK=‘D:\Backupdrive\databasename_Full_Mirror.bak’

 

Syntax:

BACKUP DATABASE prod

TO DISK=’E:\Backups\prod_Full.bak’

MIRROR TO DISK=’D:\VIYANIXAVIER\prod_Full_Mirror.bak’

 

 

10) Compression Backup:

• It can Compress the backup file.
• In SQL Server 2008, we could compress the database backups, saving disk space and time.
• 100GB CPU 10% 10min
• 25GB CPU 20% 4 min

 

Sql query:

BACKUP DATABASE database_name

TO DISK=‘D:\Backupdrive\databasename_Full.bak’

WITH COMPRESSION

Syntax:

BACKUP DATABASE prod

TO DISK=’E:\Backups\prod_comp.bak’

WITH COMPRESSION