Backup types.

Question

Hi Everyone,

I am planning a new backup strategy for databases in our new commissioned server. I am fully aware of the full backup, differential and log backup and their internals. Issue is while understanding the type of following backups

1. File Backup
2. Full file backup
3. Piecemeal backups
4. partial backup.

I know concept of these resolves around the datafile. However i am unclear of difference between Piecemeal backups and partial backup.

Can someone please help mw with this ?

I have already gone the MS docs and other articles but this is something unclear and creating more confusion.

Regards

Answer 1

Hi @Sourabh Tiwari ,

File Backup: The files in a SQL Server database can be backed up and restored individually. Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database. For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored. The damaged file can be quickly restored, and recovery is faster than it would be for an entire database.

Full file backup: A full file backup backs up all the data in one or more files or filegroups. By default, file backups contain enough log records to roll forward the file to the end of the backup operation. Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup. Below screenshot makes you better understand this. Please refer to MS document Full File Backups (SQL Server).

Piecemeal backups: There is no piecemeal backups, did you mean Piecemeal Restores? Piecemeal restore allows databases that contain multiple filegroups to be restored and recovered in stages. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. Piecemeal restore maintains checks to ensure that the database will be consistent in the end. This blog has the good example to explain Piecemeal Restores, please refer to Piecemeal Database Restores in SQL Server.

partial backup: Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup. Please refer to MS document Partial Backups (SQL Server)

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

1. + 2. In SQL Server you can backup single database files and also filegroups, see Back Up Files and Filegroups . If you e.g. a have a read-only filegroup, then you may only backup it once per month, because data can't change, and skip that filegroup in the daily backup.

3. There is no Piecemeal backup, but a Piecemeal restore, see Example: Piecemeal Restore of Database (Full Recovery Model)
4. "Partial" is just a different name for 1. + 2.