Filegroup Backups

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

view plaincopy to clipboardprint?

1. BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY'   
2. TO DISK = 'c:\Adventureworks.BAK'  

Msg 3004, Level 16, State 1, Line 1

The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -

view plaincopy to clipboardprint?

1. ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT  
2. GO  
3.   
4. BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY'   
5. TO DISK = 'c:\Adventureworks.BAK'  

Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.

Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.

BACKUP DATABASE...FILE= successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).

Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...

view plaincopy to clipboardprint?

1. DECLARE @backupPath VARCHAR(500)  
2. SET @backupPath = 'd:\sqlbackups\'   
3.   
4. DECLARE @backuptimestamp VARCHAR(30)  
5. SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')  
6.   
7. SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND  
8. FROM sys.filegroups  

view plaincopy to clipboardprint?

1.    

NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

Read more: http://sqlsolace.blogspot.com/search/label/backups#ixzz2EpPK313j