October 19, 2015 at 6:29 am
is there a way to restore all filegroups except one?
example:
Database A has 10 filegroups, but 1 of them is defunct, so i cant delete it and there's no backup for restore it.
Can I create a new DB restoring the 9 good FGs from a database A's backup?
thank u
October 19, 2015 at 6:35 am
You can, but you'll still have a defunct filegroup in the new database. The metadata of the DB knows that the database has 10 filegroups. If you restore all but one, that one will still be listed for the DB but will be unavailable and you'll get errors if you try to do anything that affects that filegroup.
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
October 19, 2015 at 6:46 am
ok, that is no good, so there's a way to get rid of this defunct filegroup?
October 19, 2015 at 6:54 am
Try taking the filegroup offline. If that works, try dropping the files in it, followed by dropping the filegroup
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
October 20, 2015 at 2:36 am
it's a cul de sac
when i try to put file offline:
"Cannot add, remove, or modify file 'v_427_COMMONS' because it is offline"
indeed it is in "7DEFUNCT" state, the file doesn't exists on server (don't know how it happens, i found this situations)
and there's no backup to restore it
October 20, 2015 at 2:56 am
🙁
I haven't played much with filegroups in that state. Let me see if I can find someone who has.
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
October 20, 2015 at 4:47 am
Not sure your trying to achieve a piecemeal restore
It allows the DBA to restore readwrite filegroups that contain transactional data without having to go thru the overhead of restoring read only filegroups. mainly meant to bring VLDBs online within the SLA.
This way files and FG that are not restored are simple ignored until the DBA adds them in later
October 20, 2015 at 5:00 am
Jayanth_Kurup (10/20/2015)
Not sure your trying to achieve a piecemeal restore
He's not. He's trying to get rid of a filegroup which is damaged (files missing) and for which there's no backup.
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
October 20, 2015 at 5:06 am
October 20, 2015 at 5:20 am
sgt: have a read through this, see if it helps.
https://msdn.microsoft.com/en-us/library/ms175122.aspx
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
October 20, 2015 at 6:59 am
ty all,
i've followed ms procedure yet, this is the situation:
select * from sys.filegroups
where name like 'fg_to_delete'
fg_to_delete 93 FG ROWS_FILEGROUP00 ....
select * from sys.database_files where data_space_id = 93
94D...FB979AE700ROWS93filenametodelX:\db\filenametodel.mdf 7DEFUNCT
alter database DB remove file filenametodel;
"Cannot add, remove, or modify a file in filegroup 'v_427_COMMONS' because the filegroup is not online."
Fg can't be online because file is defunct!
X:\db\filenametodel.mdf doesn't exists
searching around seems a common problem...
October 21, 2015 at 1:29 am
I tried the above link and it gives the message
The filegroup 'Defunct_FG' cannot be removed because it is not empty.
Way to recreate the issue ,
- Create a database , add a filegroup and a file
- set the fg to readonly and then take a backup
- perform a partial restore
RESTORE DATABASE file_group FILEGROUP='Primary' FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\file_groups.bak'
WITH PARTIAL, NORECOVERY
RESTORE LOG file_group FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\file_groups.bak' WITH RECOVERY
The filegroup should now be unuseable.
So far I tried setting emergency mode and all the options available in alter database to amke the fg online , readwrite or simply remove the file. Also tried doing backups and restores of the partially restored database in the hope of overwriting the catalog but no luck so far. Will post more info after trying out a few more things
October 21, 2015 at 2:48 am
According to someone on twitter, it can't be done (at least in SQL 2008 R2)
Why is it important that the filegroup be dropped? Is it causing errors?
Are you sure that the file's really gone? It's not renamed, or moved somewhere else?
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
October 22, 2015 at 12:05 am
October 22, 2015 at 4:30 am
indeed not a big problem, I would take advantage of a maintenance window i've obtained for this db to resove also this situation,
ty all for the support
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply