CODEWITHSUNDEEP
sql-serversql-server-2008database-restore
Martin Robins
Martin Robins

Reputation: 6163

SQL Server 2008 R2: Restore a single filegroup

Not sure if this is a duplicate of https://stackoverflow.com/questions/2951016/sql-server-2005-partial-filegroup-restore-on-qa-database, but if so then I apologise (even though it was not answered).

I have a database on SQL2008R2 with multiple (3) filegroups. There are production and development copies of this database on separate servers.

I have created a filegroup backup of the PRIMARY filegroup from production and wish to restore it on the development server. I do not want to try and backup/restore the entire database as one of the filegroups contains nearly 1TB of data and the servers are geographically distant.

I have tried restoring the filegroup backup using "with partial, recovery" and I now have access to the data in that filegroup, but I have lost access to the data in the other filegroups (I am testing this on a 3rd server for now so I have not really lost anything as such).

I am not even sure that what I am trying to do is possible (and it is starting to look as if it is not), can anybody shed any light on what else I might to to acheive my goal.

Thanks.

Upvotes: 1

Views: 1708

Answers (2)

brian
brian

Reputation: 3695

Red Gate has an object level recovery tool which would allow you to select the objects that you want to restore.

I have only used it once, but it worked very well. Very easy to use. You basically just select the backup file, select the objects to restore, select the server and database to restore them to, then you can choose to save the script or recover immediately.

Download the 14 day free trial and see if it fits your needs.

One caveat, it currently doesn't support SQL 2012 backups. I'm sure that's in the works though.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

You cannot restore the PRIMARY filegroup and keep access to the pre-restore data in the other two filegroups. It simply not how it works, and if you think a bit about data consistency you'll see that is not possible to work in any other way.

After you restored the PRIMARY filegroup you must continue with restoring the other filegroups, or not have access to them.

Upvotes: 1

Related Questions