What is backups in SQL Server? How to take all type of backup, backup syntax with example?

What is backups in SQL Server? How to take all type of backup, backup syntax with example?

1)       Full Backup: - Full backup, backs up the entire Database including Transaction Log. With full Backup Point-in-time recovery is possible because it contains .mdf and .ldf. It offers complete protection against media failures. It takes more time & requires more space to store the backup file

--FULL BACKUP: -

Backup database dbname to disk = “path”

--Example

BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak'

2)       Differential Backup: - Differential backup records only the data that has change or modified extents since the last full backup. Takes less time and requires less space. To perform the differential backup, we require at least one full backup.

--DIFF. BACKUP: -

Backup database dbname to disk = “path” with differential

--Example

 BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak' WITH DIFFERENTIAL

3)       T.Log Backup: - Transaction Log that records all transactions since last transaction log backup or differential backup. It can be used for recovering the data to the point of failure (or) specific point-in-time. These are the incremental backups; it is possible only in Full and Bulk logged recovery, models only. It is used to truncate the T.log periodically. To implement log shipping

--T.LOG BACKUP: -

Backup log dbname to disk = “path”

--Example

BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak'

4)       File/File Group backup: - File and File group backups are a specialized form of DB backup. In which only certain individual file (or) file groups from a DB are backup.

--FILE/FILE GROUP BACKUP: - APPLICABLE ONLY ON ADDITIONAL FILEGROUP, NOT ON PRIMARY FILEGROUP.

backup database <dbname> filegroup=’FG name’ to disk=’path’

--Example

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' TO DISK = 'C:\TestBackup_ReadOnly.FLG'

5)       Copy only backups: If we want backup without effecting existing plan we can use copy only backups. SQL server supports copy-only full backup and copy only t.log backup.

--COPY ONLY BACKUPS:

Backup database/Log dbname to disk = “path” with copy_only

--Example

Backup database [AdventureWorks2012] to disk = 'D:/copyonlybak.trn' with copy_only

6)       Striped backups: If the size of backup is large where there is no required disk space in any drive we can split backup into multiple files into different disks.

--STRIPED BACKUPS:

Backup database dbname to disk = “path1,to disk=’path2’ WITH NOFORMAT,STATS=10

--Example

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

7)       Mirrored Backups: In order to reduce restoration problems we can take backup of same db in multiple locations so that if any media set is get damaged we can restore from the. Maximum we can write 4 mirrors for same media set.

--MIRRORED BACKUPS:

Backup database dbname to disk = “path’, MIRROR TO disk=’path2’ WITH FORMAT

--Example 1

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'

WITH FORMAT

--Example 2

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MirrorFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MirrorFile\AdventureWorks3.bak'

WITH FORMAT

8)       Tail-log backup: The most common situations-

To perform a log backup that will skip log truncation and then take the database into the RESTORING state in an atomic state, use the NO_TRUNCATE and NORECOVERY options together.

--Example

BACKUP LOG AdventureWorks TO DISK = 'C:\Backup\AdventureWorks.bak' WITH NORECOVERY, NO_TRUNCATE

Note: We recommend using NO_TRUNCATE only when your database is damaged or corrupted. 

If database is offline and you are unable to start but you need to restore the database, than will have to back up the tail of the log. No transactions can occur during this time, using the WITH NORECOVERY option is not mandatory.

If a database is damaged, you should try to make a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option in the BACKUP statement. 

CHECKSUM: is also an option which will show you the Page Id in result where the database has corrupted. 

--Example

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks.bak' WITH CHECKSUM, CONTINUE_AFTER_ERROR

Option: -

INIT means overwrite

noinit mean append

format mean independent

noformat: both files are required

Note :- To enable checksum by default for all database backups-

       SP_CONFIGURE BACKUP CHECKSUM DEFAULT

Please comment below if missing any type of backup- Jainendra Verma