Filegroups and specifying either Full or Differential...

SQL_ME_RICH

Forgive the newbie question here, but we are doing some tests right now on backup routines, and are wanting to know if we do choose the Filegroup option to do backups with (much the way you have to do this with T-SQL or from the GUI in SSMS), is there a way to specify (a switch of some sort, or a checkbox in the Backup 7 GUI) to make sure it is a Full backup, or a Differential backup?

This is being used against a SQL Server 2005 Enterprise Edition, SP3 installation, and we have approximately 30 different Filegroups that make up various parts of the database. Just want to be able to get back to a moment in time, as accurately as possible to any kind of data loss outage, in similar fashion to an old-school Full/Diff/TLog routine.

Thank you, and forgive me if I missed this in searching your topics out here (I didn't see anything straight away along these lines...).

SQL_ME_RICH

Marianne

Hi,

Selecting the 'Filegroups or files' option in the Back Up or Schedule Backup Jobs wizards will create a full filegroup backup.

To take a differential filegroup backup, add WITH DIFFERENTIAL to the BACKUP command. There isn't an option in the wizards to take a differential filegroup backup, but you can include this option in the extended stored procedure or command line instruction. If you want, you can use the Back Up wizard to generate the BACKUP command without running it:
- Select the relevant options to take a filegroup backup of the relevant files of your database.
- Copy the command (either as an extended stored procedure or command line instruction) from the script tab on the final step of the wizard, then cancel the wizard so that you don't take the backup yet.
- Edit the command in an application such as Management Studio to add WITH DIFFERENTIAL (or any other options you require).
- Execute the command.

If you are creating a scheduled backup job, you can create the job using the Schedule Backup Jobs wizard, then edit the job in Management Studio to add WITH DIFFERENTIAL to the BACKUP command (you may want to edit the job name from the wizard so you can find it easily in Management Studio). If you need to edit the job again, you can use the SQL Backup GUI - any additional options which are not supported by the GUI (such as WITH DIFFERENTIAL for filegroup backups) will be preserved but not editable.

More information on the BACKUP command syntax is available here: http://www.red-gate.com/supportcenter/C ... BACKUP_cmd

I hope that helps.

Thanks,

Marianne

rwyarger

Thank you Marianne -

I was beginning to wonder if I didn't need to first do a Full-Backup, and then a Full Filegroup Backup. Had seen that in a couple of older threads, but your description makes perfect sense to me.

Thanks for the command line reference as well. It will come in handy.