Comments (19)
Based on the discussion in this thread I have created a pull request which adds single filegroup backup support, including support for backing up memory-optimized filegroups together with the PRIMARY filegroup: #217
from sql-server-maintenance-solution.
Would you like to be able to back up multiple filegroups in the same backup command and backup file (e.g. back up PRIMARY, FG1 and FG2)?
from sql-server-maintenance-solution.
from sql-server-maintenance-solution.
Any thoughts on how to do with directory paths and backup file names?
It would be nice to have the names of the filegroups in the directory path and / or file names, but then you run into the limitation for a backup device (259 characters).
I could make a token for the file groups, e.g. {FileGroups}. It may work in some cases, but in some cases it may be too long.
from sql-server-maintenance-solution.
from sql-server-maintenance-solution.
I was thinking some more about this. Let's say that you want to back up the filegroups PRIMARY, FG1 and FG2, and you specify that in a filegroup parameter.
Then there are two ways of doing this in the stored procedure:
-
Perform one backup command, and specify the three filegroups. You would end up with one backup file.
-
Perform three backup commands, one backup command for each filegroup. You would end up with three backup files.
Option 2. would make directory names and file names easier, as it is only one filegroup for each backup file.
What other pros and cons are there?
from sql-server-maintenance-solution.
from sql-server-maintenance-solution.
I think that the parameter could work the same as in DatabaseIntegrityCheck.
https://ola.hallengren.com/sql-server-integrity-check.html#FileGroups
That supports exclusions and also wildcards.
from sql-server-maintenance-solution.
Probably the best is option 2, that way you can handle fg(s) independently.
from sql-server-maintenance-solution.
Would not option 1 basically include option 2, if you just specify one filegroup?
from sql-server-maintenance-solution.
If you just specify one filegroup, then there is only one way of doing it.
The question is how it should work if you specify multiple filegroups.
from sql-server-maintenance-solution.
On the back paths and file names, I don't have an answer for you. If it's longer than 259 characters, I'd say disallow and give an error message saying combined length too long. Actual filegroup names are not hard to change. If people really need this functionality, they will deal with it.
from sql-server-maintenance-solution.
Option 2 would be my preference, where you can individually backup each filegroup as required. Maybe a top level directory of FG and then the filegroup name as the subdirectory. Thanks for looking into this Ola.
from sql-server-maintenance-solution.
In my opinion the second solution it the best and I think that the athurgar's idea to have a top level directory FG is good. After that I do not know if it is better to have subdirectories for each FG or not. Thanks Ola!
from sql-server-maintenance-solution.
One of the original questions I posed to Ola was the concept of removing a partition from a normal backup. Take Ola's example of PRIMARY, FG1 and FG2. Let's say that PRIMARY is 50GB of always used data, FG1 may be a special FG for XML data (for example) maybe 20GB and growing slowly, FG2 is for archived tables and is 1TB and only required for audit purposes - data never gets updated or deleted, maybe new data once a year. A normal backup would be 1070GB. But in reality I only want to backup PRIMARY and FG1 every night and not FG2. The only way that this can be accomplished at present is to have FG2 as its own database. But this does not meet the security requirements for this company that requires all the data to be in this one database. Nightly backups take forever - but really shouldn't need to as only 70GB of data needs to be backed up, not the extra 1TB.
from sql-server-maintenance-solution.
I was thinking that you should be able to do things like this:
@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'
from sql-server-maintenance-solution.
One complexity that I found is that primary and in-memory filegroups have to be backed up together.
"A BACKUP or RESTORE DATABASE statement that includes the primary filegroup must include the MEMORY_OPTIMIZED_DATA filegroup, and vice versa."
from sql-server-maintenance-solution.
Support for specific filegroups would be great. We currently do backups of individual filegroups which gives us some flexibility on the restore side. We can exclude certain filegroups if we want to create a metadata only copy of our databases (e.g. staging environment) without pulling backups for some of the other filegroups which can be quite large (blob data, logging etc).
We put all the filegroup backups into a single folder. We name the files with the following format:
{DatabaseName}FG{BackupType}{FileGroupName}{Date}{Time}_{FileNumber}of{NumberOfFiles}.BAK
e.g.
MyDatabase_FGFULL_MyFileGroup_20180725_120000_1of4.BAK
I have a SP to handle the filegroup backups for this specific DB but use your DatabaseBackup SP for the other DBs.
In terms of the backup I actually want all read/write filegroups but as separate backups for each filegroup.
from sql-server-maintenance-solution.
I was thinking that you should be able to do things like this:
@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'
We startet to use the filestream feature of sql server. And now we would like to seperate the filestream filegroup backup from the data filegroups backup. So options like
@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'
or
@FileGroups = 'Db1.FileStreamGroup'
would be fine.
from sql-server-maintenance-solution.
Related Issues (20)
- Index maintenance : Skip filegroup / partition HOT 1
- Index Maintenance Miscalculating Page Numbers
- Create Restore SP
- EMC data Domain: AG backups. skip database part of same AG doesn't work.
- Exclude Multiple tables
- Include the name of the day, for daily differential backups.
- Default Backup Location Folder doesn't exists HOT 1
- Fallback backup location
- ChangeBackuplocation
- Add an option to generate the Task Scheduler commands for SQL Express
- Update Statistics very slow on a 3Tb database HOT 3
- PRINT when TimeLimit has been reached in IndexOptimize HOT 1
- Diff backup of new database created fom another database is not being converted to full.
- (JOBNAME) variable not working on SQL Server 2014? HOT 2
- ColumnStore Index Fragmentation Issue
- Support Veeam Databses backups
- IndexOptimize job randomly reporting failure even when the log file says the Outcome succeeded HOT 6
- running rebuild on individual index takes a long time compared to native SQL commands HOT 2
- Enhance Index Rebuild Decision Process with Page Density Factor
- Parallel processing for index maintenance
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql-server-maintenance-solution.