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=
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.