Shahid Naeem

File/Filegroup Backups

1. A file or filegroup backup does not back up any portion of the transaction log. In
   order to restore a file or filegroup backup, you must have the transaction log
   backups since the last file or filegroup backup, including the tail of the log, in order
   for the database system to ensure transactional consistency. This also implies that
   the database must be in full or bulk-logged recovery because these are the only
   models that support transaction log backups.
2. Individual file or filegroup backups can be restored from a full database backup.
3. Point-in-time recovery is not permitted with file or filegroup backups.
4. 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.

EXAMPLE

BACKUP DATABASE [AdventureWorks] FILE = ‘AdventureWorks_ReadOnlyData’
TO DISK = N’C:\mssql2005\backup\AdventureWorks_ReadOnlyData.bak’
WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Readonly File Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

This entry was posted on Thursday, February 21st, 2008 at 5:05 am and is filed under Backup Database, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.