August 17, 2010 at 5:34 pm
Greetings everyone,
I am building a database across multiple file groups and there are situations where I need to backup file groups and in the future restore them individually if need be. I can backup the files and even restore the filegroups, however, now matter how hard I try the fielgroups restore in an offline state only and I can not get them back online.
After doing a long search through google and following several peoples examples I have gotten no farther than I was when I started.
here is the backup code;
backup database datawarehouse
filegroup = 'FG1-1'
,filegroup = 'FG1-2'
TO DISK = 'g:\fg.bak'
I also do this (as advised in my BOL topics)
BACKUP DATABASE Datawarehouse filegroup = 'Primary' to disk = 'G:\Primary.bak'
backup log Datawarehouse to disk = 'G:\log.bak' with norecovery
This all works fine,
And when I execute this
RESTORE DATABASE Datawarehouse
FILEGROUP = 'FG1-1'
,FILEGROUP = 'FG1-2'
FROM DISK = 'g:\fg1.bak'
WITH RECOVERY, stats = 10
It works fine as well. However when I go to select data from those filegroups then I get this error message;
'The query processor is unable to produce a plan for the table or view 'Item' because the table resides in a filegroup which is not online.'
No where do I see an option or ability to bring a file online. I can see SQL scripts online setting them to offline, but nothing restoring it to online.
In one article a person stated that simply issuing; RESTORE DATABASE datawarehouse FILEGROUP = 'FG1-1', FILEGROUP = 'FG1-2' WITH RECOVERY
would bring it online but I get more errors 'The file or filegroup "FG1-1" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed.'
Am I missing something here? Why is backing up and restoring file groups so overly complicated? After reading BOL and a few microsoft tech articles several times I have gained no new knowledge.
Link to my blog http://notyelf.com/
August 17, 2010 at 11:12 pm
Check if this helps.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 18, 2010 at 1:29 pm
It does and doesn't. If you can not restore only part of a database via file group...then what exactly is the functionality of making filegroup backups and restores?
Link to my blog http://notyelf.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply