Friday 23 July 2010

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
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 -

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
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 ...


DECLARE @backupPath VARCHAR(500)
SET @backupPath = 'd:\sqlbackups\' 

DECLARE @backuptimestamp VARCHAR(30)
SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')

SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND
FROM sys.filegroups
 
NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

No comments: