Partial Backup

Partial Backup

We can use partial backup  while handling  large databases
Using partial backup we can take backup of only read write enabled filegroups instead of
all filegroups which are read_only.


As a large database we have possibility that some filegroups will have archived data which
is read_only and not updated frequently
We don't need to take its backup regularly so using partial backup we can avoid taking its backup
frequently

Lets see how to take partial backup

We have a database named backup_test

We have two filegroups except [primary] named [FIRST] and [SECOND]

Here we have changed [SECOND]to read_only

We can take partial backup for this

Note:-  We have to define  "READ_WRITE_FILEGROUPS" while taking partial backup
        We cant take partial backup using management studio.


First we will take full backup which will include  files from all filegroups including files from [second]
filegroup which is read_only. We can see in below image

BACKUP DATABASE [backup_test]  TO 

DISK = N'C:\Backup\backup_test_full.bak'

WITH NOFORMAT, NOINIT,  NAME = N'backup_test-Full Database Backup',SKIP, NOREWIND, NOUNLOAD,  STATS = 10

so now onwards we need to take backup of primary and first filegroups  which are read write enabled
Now we will take partial backup. Here files from [SECOND] filegroups are not included.

BACKUP DATABASE [backup_test] READ_WRITE_FILEGROUPS TO 

DISK = N'C:\Backup\backup_test_partial.bak'

WITH NOFORMAT, NOINIT,  NAME = N'backup_test-Full Database Backup',SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Now lets see file details from backup 

RESTORE filelistonly FROM DISK = N'C:\Backup\backup_test_full.bak'

RESTORE filelistonly FROM DISK = N'C:\Backup\backup_test_partial.bak'

Here we can see that  files from [SECOND] filegroup is not included, value of ispresent = 0 for [second_1] file from [SECOND] filegroup in partial backup while  its included  in full backup