FileGroup Backups in SQL Server 2012
Much of our discussion thus far has focused on backing up an entire DB, but it is possible to back up only particular files or a group of files in a filegroup. A SQL Server DB, by default , has only two files: the data file (with the file extension .mdf) and the log file (with the extension .ldf). You can add additional files and filegroups that contain these files to extend the DB beyond the original two files. Thes e additional files are often data files added to larger DBs that require additional space. With very large DBs, performing a ful l backup tha t contains all the DB files can take too much time. In such a case, the individual files or filegroups can be backed up separately, enabling you to spread out the backup.
BACKUP DB [AdventureWorks2012] FILE = 1
AdventureWorks2012_ReadOnlyData'
TO DISK = N'E:\mssql2012\backup\AdventureWorks2012_ReadOnlyData.bak1
WITH NOFORMAT, INIT, NAME = N1
AdventureWorks2012-Readonly File Backup1
,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
There is some additional administrative overhead associated with file and filegroup backups. Unlike a full DB backup that produces one file that contains the entire DB, the file backups do not stand by themselves and require other backups to be able to create the entire DB. You need to keep the subsequent points in mind when performing file and filegroup backups:
-
A file or filegroup backup does not back up any portion of the transaction log. To restore a file or filegroup backup, you need to have the transaction log backups since the last file or filegroup backup, including the tail of the log, for the DB system to confirm transactional reliability. This also implies that the DB need to be in full or bulk-logged recovery because these are the only models that support transaction log backups.
-
Individual file or filegroup backups can be restored from a full DB backup.
-
Point-in-time recovery is not permitted with file or filegroup backups.
-
Differential backups can be combined with file or filegroup backups. These differential backups capture only those extents that have changed since the file or filegroup backup was made.
File and filegroup backups can be very powerful options for very large DBs, but you need to confirm that the relevant backups can be accounted for. In all backup situations, the key to a successful plan is testing your backup strategy; this is particularly true with file and filegroup backups.