Backup Part-1

What are the different types of Backups?

·  Data backup

·  Database backup

·  Full backup

·  Copy-only backup

·  Differential backup

·   Log backup

·   File backup

·    Partial backup

 What is the meaning of the values in the Type column in backupset table?

·   D – Database

·    I – Differential database

·    L – Log

·    F – File or filegroup

·    G – Differential file

·    P – Partial

·    Q – Differential partial

 Explain the below Backup script?

USE master

GO

BACKUP DATABASE [Test] TO DISK = N'D:\ Backups\ test_full_native_1.bak'

WITH FORMAT, INIT, NAME = N'test- Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

FORMAT – This option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized (NOFORMAT will preserve it).

• INIT – By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it’s useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.
• NAME – The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.
• SKIP – Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn’t care if any backups existing in the backup set have been marked for availability to be overwritten.
• NOREWIND – This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.
• NOUNLOAD – When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.

5.  What is the differential backup

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, 
including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

Benefits

·   Creating a differential backups can be very fast compared to creating a full backup. A differential backup records only the data that has changed since the full backup upon the differential backup is based. This facilitates taking frequent data backups, which decrease the risk of data loss. However, before you restore a differential backup, you must restore its base. Therefore restoring from a differential backup will necessarily take more steps and time than restoring from a full backup because two backup files are required.

·   Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. In these cases, differential database backups enable you back up frequently without the overhead of full database backups.

·   Under the full recovery model, using differential backups can reduce the number of log backups that you have to restore.

Please visit other related articles...

https://sqldbaiq.blogspot.com/p/backup-iq.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-2.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-3.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-4.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-5.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-6.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-7.html

https://sqldbaiq.blogspot.com/p/backuprestore-part-8.html

References: Thanks to the all the SQL Server bloggers who wrote and shared the valuable information on their blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.