June 22, 2016 at 8:41 am
I'd like to split a DB into 2 filegroups, PRIMARY & HISTORY
HISTORY would contain one large table, this table > 99% of the total DB size.
The DB is in simple recovery mode.
I think the answer is No, but would like confirmation.
Is it possible to restore a filegroup backup onto another Server?
I often have to copy this data to another server, being able to copy 200MB rather than 20GB is really advantageous.
Thanks for reading my post
June 22, 2016 at 8:48 am
Yes, you can, but you need to restore the full backup first, so I suspect that would defeat the object. You might consider putting your HISTORY table in a separate database instead.
John
June 22, 2016 at 9:01 am
Thanks John, you are right, another DB would be best, but not an option 🙁
The large big db gets backed up daily.
Say I restore monday's full backup (30GB) to my local machine. On Friday I take a filegroup backup and restore to my local machine, with monday's data, will this work?
Are you saying I would need Friday's Full backup too. Which in this case does defeat my purpose.
Cannot think straight on this one, how can you restore only some filegroups if there are referential constraints between tables in the 2 filegroups? there aren't in my case..
Thanks
June 22, 2016 at 9:19 am
terry999 (6/22/2016)
Is it possible to restore a filegroup backup onto another Server?
Only if you start by restoring primary and, since the DB is in simple recovery, all the filegroups you restore have to be part of the same backup (log backups are needed when restoring from multiple different filegroup backups)
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
June 22, 2016 at 9:23 am
terry999 (6/22/2016)
Say I restore monday's full backup (30GB) to my local machine. On Friday I take a filegroup backup and restore to my local machine, with monday's data, will this work?
No, as you're in simple recovery model.
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
June 22, 2016 at 9:30 am
As per Gail, https://msdn.microsoft.com/en-us/library/aa337540.aspx
Read this before you do anything.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply