issues/38 Add support for filegroup backup to DatabaseBackup

[JonasNjopOlsson]

Solution overview

Fixes issue https://github.com/olahallengren/sql-server-maintenance-solution/issues/38

• My primary use case: Backup read-only filegroups in a VLDB (very large database) with demands on short restore times for each filegroup separately. Placing more than one large filegroup into a backup file increases the restore time significantly for that filegroup according to my tests.
• The suggested solution only allows backup of one specified filegroup at a time. This simplifies how to name the backup files and/or directories.
• The solution also takes into account how to handle memory-optimized filegroups so that the PRIMARY filegroup is always backed up together with it. One limitiation is that it is not possible to perform the required checks for memory-optimized filegroups on non-readable secondaries in AlwaysOn Availability Groups. The backup of a memory-optmized filegroup will fail in those cases.

Support added for the following backup software:

• SQL Server native backup
• RedGate SQL Backup Pro (not tested)

Support not added for the following backup software:

• DELL EMC Data Domain Boost
• Quest LiteSpeed for SQL Server
• Idera SQL Safe Backup

I have only been able to test SQL Server native backup with the suggested changes. However, RedGate's backup software seems to follow the same syntax as the native backup solution.

I have not been able to find documentation on or references to how to perform backups of multiple filegroups for the other backup sofware solutions (which is required for the memory-optimized filegroup support).

[JonasNjopOlsson]

I have now updated the code in this pull request so that it merges cleanly with the latest commit to the upstream master branch on 2019-04-28.

[JonasNjopOlsson]

This pull request now merges cleanly with the latest commit to the upstream master branch on 2019-06-14.

[ianmanton]

Is there a way to upvote this? Would be super useful!

[JonasNjopOlsson]

This pull request has been updated to merge cleanly with the upstream master branch. Additional checks for supported types of filegroup backups for databases in SIMPLE recovery model have also been added.

[ianmanton]

@JonasNjopOlsson

Thanks for your dev work here - I'm trying to run this as a test on our own Dev server - as I'd like to backup and restore individual FGs as part of an approach to reduce VLDB into manageable chunks - can this actually be done with this mod though? What parameters / accepted values are required to be added to the execution statement to run the backup of (ideally all FGs in a db) or specific named ones?

Thanks

[JonasNjopOlsson]

@JonasNjopOlsson

Thanks for your dev work here - I'm trying to run this as a test on our own Dev server - as I'd like to backup and restore individual FGs as part of an approach to reduce VLDB into manageable chunks - can this actually be done with this mod though? What parameters / accepted values are required to be added to the execution statement to run the backup of (ideally all FGs in a db) or specific named ones?

Thanks

Hi @ianmanton,

Regarding multiple filegroup support, I mentioned in this thread as part of the original description of the code, that I chose to only allow backup of one filegroup at a time, with the exception of any memory-optimized filegroup which must be backed up together with the PRIMARY filegroup. The reasons are:

• Avoiding code complexity - Although not hard to add multiple filegroup support, I strive for simplicity when adding something to Ola's code since that is what I see in what he has done already and which is one of the reasons for the great stability and adoption of his maintenance solution.

• Clear backup file naming - When backing up a single filegroup at a time, it is easy to include the filegroup name as part of the target backup file name. If you are allowed to include several filegroups at a time, I don't see a clear way of naming the backup files using the standard naming as laid out by Ola.

• The solution is intended for VLDBs (very large databases), where one filegroup usually will contain a lot of data. In my particular use case, I'm backing up a several TBs large database with yearly filegroups containing hundreds of GBs data each.

If you need to backup several read-only filegroups, it is rather easy to create a loop in T-SQL that backs up the filegroups one-by-one after each other using the DatabaseBackup stored procedure and a query that inventories the appropriate filegroups. That is what I am doing in the backup job that is run yearly when a new filegroup has been marked read-only.

[ianmanton]

Thanks Jonas

[JonasNjopOlsson]

This pull request has now been updated to include the latest changes as of today (2022-01-07) from the main branch and to merge cleanly with it.

[mvomhau]

Hello, I have a DB with a Primary, FileStream and Memory Optimized Tables filegroup. Full backups works fine, but the restore of the FileStream takes ages. So I want just to make backups of the Primary and the Memory Optimized Tables filegroup because both are neccessary. Is that possible with DatabaseBackup and if so, how?

backup database myDB 
  FILEGROUP = N'PRIMARY', 
  FILEGROUP = N'MemOptTables' 
    to DISK = N'...' 
    with ...

[JonasNjopOlsson]

Hello, I have a DB with a Primary, FileStream and Memory Optimized Tables filegroup. Full backups works fine, but the restore of the FileStream takes ages. So I want just to make backups of the Primary and the Memory Optimized Tables filegroup because both are neccessary. Is that possible with DatabaseBackup and if so, how?

backup database myDB 
  FILEGROUP = N'PRIMARY', 
  FILEGROUP = N'MemOptTables' 
    to DISK = N'...' 
    with ...

Hi @mvomhau,

What is your restore plan for the database using such a backup strategy? If there are errors in the database or you need to restore it for some other reason, how will you recover both the data in the FILESTREAM filegroup and the other filegroups?

I recommend that you check https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/piecemeal-restores-sql-server and other sources for more information on piecemeal restores of SQL Server databases to understand the restrictions. Then test your restore strategy before changing your backup strategy.

That being said, the filegroup feature that I have added in this pull request only supports backing up one filegroup at a time using the new filegroup parameter. However, the filegroup for memory optimized tables is special and requres you to always back it up together with the PRIMARY filegroup.

This pull request checks for a filegroup for memory optimized tables and always backs it up together with the PRIMARY filegroup. You can specify either PRIMARY or the name of the memory optimized filegroup in the filegroup parameter and the results will be the same using either alternative: both filegroups will be backed up together.

[mvomhau]

Hi JonasNjopOlsson,

thanks for the reply and the change of the procedure. I tested it and it seems to work.

The filestream data are not that critical and we can recover them from other file sources. So in a worst case scenario we can restore the primary (and memory optimized tables) and bring them quickly online and deal with the filestream data later. We also make a full backup every weekend with the filestream data where we can recover most of the filestream data.