Partial Backups
If data is placed on a read-only file group, you can use partial backups to backup everything except read-only data.
Partial Backups Scenario
--Need to add a read only filegroup first to demonstrate ALTER DATABASE AdventureWorks ADD FILEGROUP ReadOnlyFG1 GO -- Add a file to the Filegroup ALTER DATABASE AdventureWorks ADD FILE ( NAME = AdventureWorks_ReadOnlyData, FILENAME = ‘C:\mssql2005\data\AdventureWorks_ReadOnlyData.ndf’, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP ReadOnlyFG1 go --Create a table on the ReadOnly filegroup CREATE TABLE Adventureworks.dbo.MyReadOnlyTable ( FirstName varchar(50), LastName varchar(50), EMailAddress char(1000) ) ON ReadOnlyFG1 --Insert some data into the new read only Filegroup insert Adventureworks.dbo.MyReadOnlyTable select LastName, FirstName, ‘xxx’ from Adventureworks.person.contact --Make the filegroup readonly ALTER DATABASE [AdventureWorks] MODIFY FILEGROUP [ReadOnlyFG1] READONLY
ExampLE
--Need to backup the readonly filegroup the was created -- or it will be included in the partial backup BACKUP DATABASE [AdventureWorks] FILEGROUP = N’ReadOnlyFG1’ TO DISK = N’C:\mssql2005\backup\AdventureWorks_ReadOnlyFG.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks-Full Filegroup Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 --Create the Partial Database Backup --It will not contain the data from readonly filegroup --The partial database backup can be restored without affecting -- the data in the readonly filegroup BACKUP DATABASE [AdventureWorks] READ_WRITE_FILEGROUPS TO DISK = N’C:\mssql2005\backup\AdventureWorks_Partial.bak’ WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Partial Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 --It is possible to backup the readonly filegroup(s) as well --by listing the readonly filegroups in the backup command as shown in the --following backup command BACKUP DATABASE [AdventureWorks] FILEGROUP = ‘ReadOnlyFG1’, READ_WRITE_FILEGROUPS TO DISK = N’C:\mssql2005\backup\AdventureWorks_Partial_WithReadOnly.bak’ WITH NOFORMAT, INIT, NAME = N’AdventureWorks-Partial Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
Tags: Partial Backup