January 12, 2011 at 7:59 am
Hello, I have a question about backing up and restoring filegroups. An application wants to be able to restore individual filegroups without restoring other filegroups. Hypothetical: most tables are in the Primary filegroup. Reporting tables are in the Reporting filegroup. I take full backups nightly and also take a backup of each filegroup. Say one day I need to restore the Reporting filegroup and leave everything else as is. When I experiment with this, and try to only restore the Reporting filegroup, SQL says it can't restore the file in the Primary filegroup because it is not in the backup.
But I don't want to restore the file in the Primary filegroup. I want to leave it unrestored. I have read up on this in BOL but have not found the answer. Can you restore one filegroup and leave the Primary unrestored? thanks
January 12, 2011 at 8:06 am
You have to restore primary. Primary is where all the system tables are and without those the database would be useless.
You need to restore primary, then one of more filegroups, then enough transaction logs that the filegroups are brought to a consistent 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 12, 2011 at 8:27 am
Thanks for the response. So you always have to restore the primary filegroup and can optionally restore the other filegroups.
So in my example, to only restore the reporting tables, I should put the reporting tables in Primary and put all other tables in another filegroup. Then I can restore Primary, not restore the other filegroup, and therefore get the reporting tables back to a previous state and leave everything else unchanged. Correct?
January 12, 2011 at 8:42 am
You have to have primary available, and then you can restore another filegroup.
So if you have your db at 5:00pm. Some things in Primary, some in Reporting. If you have take a filegroup backup of Reporting at 11:00pm, then you have changes. If you then at 10:00am the next morning want to restore the Reporting filegroup from the previous backup, you can without changing Primary.
January 12, 2011 at 11:48 am
Ken Davis (1/12/2011)
So in my example, to only restore the reporting tables, I should put the reporting tables in Primary and put all other tables in another filegroup. Then I can restore Primary, not restore the other filegroup, and therefore get the reporting tables back to a previous state and leave everything else unchanged. Correct?
You can, but it won't leave the rest of the DB unchanged. Restoring primary means restore database and it will overwrite the database if it's already there (or give you an error saying WITH REPLACE must be specified)
You cannot use file/filegroup restores to restore pieces of a database to a different time to the rest. If you restore primary from one backup and another filegroup from a different backup then you also need to restore log backups that cover the period between the two backups you used. So you can't restore primary using today's backup, the reporting filegroup using one from 2 weeks ago and have the DB online and usable with the reporting tables as they were 2 weeks ago.
What you can do is restore primary + reporting filegroup (and I would suggest putting them into a separate filegroup, not primary) and then use something like Redgate's SQLDataCompare or some custom scripts to copy the older data into the main DB.
Or split the reporting into another DB, then you can restore it as you please.
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 12, 2011 at 12:23 pm
Ok. It sounds like file/filegroup restores are not the solution I need.
I am going to create another thread describing my scenario. Feel free to comment on it!
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply