In
SQL Server we have an option of taking backup of only certain part of the
database, something like if we have a database which has multiple file groups
and we want to take backup of only a single FileGroup that can be achieved. It is
termed as Filegroup backup or Partial backup.
This
kind of backups are needed in situations like if we have a large table in
separate file group of production database and that table is not required to be
available in test environment, so to save space in test environment and to reduce backup/restore
times we can only take the backup of required file group and restore it to test
environment.
FILEGROUP BACKUP:
BACKUP DATABASE
AdventureWorks2012
FILEGROUP =
'PRIMARY'
TO DISK
= 'C:\SQL2K8\Backups\AdventureWorks2012.bak'
WITH INIT,COMPRESSION
The
backup command is almost same as normal backup command it will have section
named ‘FILEGROUP’, in this section we need to mention the file group name which
we want to backup. With this command SQL engine will back up only the file group name
we specify, in above case only PRIMARY file group will be backed up.
PARTIAL RESTORE:
RESTORE DATABASE
AdventureWorks2012_Test
FROM DISK
= 'C:\SQL2K8\Backups\AdventureWorks2012.bak'
WITH PARTIAL,
MOVE 'AdventureWorks2012_Data'
TO 'C:\SQL2K8\Backups\AdventureWorks2012_Data.mdf',
MOVE 'AdventureWorks2012_Log'
TO 'C:\SQL2K8\Backups\AdventureWorks2012_log.ldf',
RECOVERY,REPLACE,STATS=10
While
restoring the PARTIAL backups the only difference from normal restore command
is we need to specify the option ‘PARTIAL’ in the restore command and rest of
the command is same.
Points
To Remember:
·
Database should be in FULL recovery in order to perform PARTIAL
backup.
·
In partial restore one thing to note is, if the database has 2
file groups like PRIMARY and SECONDARY and we restored a PARTIAL backup which
has backup of only PRIMARY file group, after restore through GUI we can still
see the SECONDARY file group which was non-existent in backup file we used. But
if we try to query the objects of that FG it throws error. Explained in below
captures. So, even though we can see the FG through GUI
it doesn't exist physically.
Performed
a PARTIAL restore as shown in above capture using backup file which has only
PRIMARY file group backup.
After
restore when I verified properties of database it still shows SECONDARY FG.
Now
I try to query the ‘SalesOrderDetail’ table which resides in SECONDARY FG, as the
FG is not part of the partial backup we used the query throws below error:
Thanks!!
