January 27, 2016 at 1:11 pm
A little background, a conversion project is being started.
The company leading this is recommending having all the data in one database.
We have a couple of system that use filegroups and because of the volume of data
I'm looking at whether to create filegroups to split up the data.
My main concern is if we need to recover some data as batch processing occurs.
If a program errors, we may have to reset the data that program may have updated
to just before the program ran and then re-run the program. Other programs may have run
that alter other data, which is why I don't want to restore the entire database to the same point-in-time.
I haven't been able to successfully do this type of restore in testing. It appears the system wants to apply
the log to bring everything(all filegroups) up to the same point. Reading various articles seems to confirm this.
We'll probably use 2012 SP2 or 2014 as the edition levels. I'd just like to confirm
that it's not possible to restore a particular filegroup to point-in-time than the other
filegroups may be.
TIA,
Doug
January 27, 2016 at 3:13 pm
For SQL Server, integrity and consistency are like holy grails.
So even if you use different filegroups, SQL Server will still insist that they be consistent. When restoring you can do the filegroups one by one in order to bring the most urgent data back online the quickest, but the filegroups that have not been restored to the same point in time will be made inaccessible.
If you want the ability to restore some of your data to a point and time and other data to a different point in time, then you'll have to use separate databases. (Can be on the same instance, though). And prepare to manually face and fight the inconsistencies if you ever do need to restore. (Because the down side of splitting out the data over multiple databases is that it is almost impossible to get a restore done and guarantee that the databases are still consistent).
January 28, 2016 at 2:01 am
dough-378918 (1/27/2016)
I'd just like to confirmthat it's not possible to restore a particular filegroup to point-in-time than the other
filegroups may be.
Correct, it's not possible.
When any part of the database is restored, log backups have to be restored as well to bring the entire database back to the same point in time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2016 at 6:45 am
From a backup/restore perspective then, especially to try to restore a portion of a system to a certain point-in-time, is there
any reason to use Filegroups?
In this particular application, the only reason I was looking into using Filegroups was to see if I could have better control over
restoring pieces of the system. I would normally create multiple databases to logically split out the data, but a 3rd party vendor
we are working with is used to having everything in one database. I'm just trying to see if having everything in one database would
be in our best interest.............I'm thinking not.
Thanks for everyone's replies.
Doug
January 28, 2016 at 7:05 am
dough-378918 (1/28/2016)
From a backup/restore perspective then, especially to try to restore a portion of a system to a certain point-in-time, is thereany reason to use Filegroups?
No. If you're required to be able to restore a subset of the data to an earlier point in time, you need separate databases, not multiple filegroups.
Filegroups have lots of uses w.r.t. backup/restore, but your requirement is not one of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply