In my last post, I demonstrated how to mount a database with missing NDF files. In the end, we still have issues removing tables created over missing files, the files are taken offline. Be cautious of taking files offline since there is no (official) way to bring it back online. In either situation, you will need to modify/correct SQL Server metadata. This is allowed in SQL Server 2000 with “Allow ad hoc update” option turned on. However, it’s deprecated in SQL Server 2005 and removed in SQL Server 2008 and upper versions.

Now, let’s borrow the script in my last post to create the test environment

use master
if db_id('TestDB') is not null
drop database TestDB
go
create database TestDB on  PRIMARY ( name = 'TestDB', filename = 'C:\Temp\TestDB.mdf'), 
 filegroup FG1 ( name = 'TestDB_File1', filename = 'C:\Temp\TestDB_File1.ndf')
 log on ( name = 'TestDB_log', filename = 'C:\Temp\TestDB_log.ldf')
GO
create table TestDB.dbo.a (id int) on [PRIMARY]
insert into TestDB.dbo.a values(1)
create table TestDB.dbo.b (id int) on [FG1]
insert into TestDB.dbo.b values(2)
go
select * from TestDB.dbo.a -- return one record id = 1
select * from TestDB.dbo.b -- return one record id = 2
go

Now let’s turn the file TestDB_File1.ndf offline.

alter database TestDB modify file (name = 'TestDB_File1', offline)
go
--verify the database is offline already.
select * from TestDB.dbo.b
--Msg 8653, Level 16, State 1, Line 1
--The query processor is unable to produce a plan for the table or view 'b' because the table resides in a filegroup which is not online.
--This is the expected error message 
go
--bring it back online ?
alter database TestDB modify file (name = 'TestDB_File1', online)
--Msg 155, Level 15, State 1, Line 1
--'online' is not a recognized CREATE/ALTER DATABASE option.
go
--'online' is not a recognized CREATE/ALTER DATABASE option.

There is no option bringing a offline file online. The only way to bring it up is to modify system metadata, which is NOT suggested. Now let’s shutdown the services and start it in single user mode

sqlservr.exe -c -m

Then connect to the instance using Dedicated Admin Connection (use SSMS, enter admin:InstanceName to Server name, choose SQL Server Authentication, then use sa and password to login to the instance)

use master
select name, state, state_desc from sys.master_files where database_id = db_id('TestDB')
--name             state state_desc
------------------------ -----------
--TestDB           0     ONLINE
--TestDB_log       0     ONLINE
--TestDB_File1     6     OFFLINE
--(3 row(s) affected)
--Internally, file statuses are saved in sys.master_files system table which is accessed by sys.sysbrickfiles view. 
select lname, filestate from sys.sysbrickfiles where dbid = db_id('TestDB')
--lname             filestate
-----------------------------
--TestDB            0
--TestDB_log        0
--TestDB_File1      6
--(3 row(s) affected)

The only thing is to bring the file ONLINE is to modify the filestate of TestDB_File1 to 0.

update sys.sysbrickfiles set filestate = 0 where dbid = db_id('TestDB') and lname = 'TestDB_File1'
--Warning: System table ID 22 has been updated directly in database ID 1 and cache coherence may not have been maintained. SQL Server should be restarted.
--(1 row(s) affected)
--Even you've changed the system metadata, the file will not be online immediately. You will have to re-load the database
alter database TestDB set offline
alter database TestDB set online
select * from TestDB.dbo.b
--id
-------------
--2
--(1 row(s) affected)

Done! You have brought the offline file online. This is not a documented feature. Once you bring the server under single user mode, with admin connection, you can modify all system internal tables.

Now you are able to change the status to make the file online. You can also remove tables using the same way. But it’s more complicated that many things will have to be considered such as partition(rowsets), table definitions, indexes, statistics, keys etc. Here is the code for this case

use master
---get object id and partition id is enough in this case
select object_id('TestDB.dbo.b') -- object_id = 2121058592
select * from TestDB.sys.sysrowsets where idmajor = 2121058592 -- rowsetid = 72057594038845440
-- start deleting rows from metadata
set xact_abort on
begin transaction
delete TestDB.sys.sysobjvalues where objid = 2121058592
delete TestDB.sys.sysiscols where idmajor = 2121058592
delete TestDB.sys.syscolpars where id = 2121058592
delete TestDB.sys.sysschobjs where id = 2121058592
delete TestDB.sys.sysidxstats where id = 2121058592
delete TestDB.sys.syssingleobjrefs where depid = 2121058592
delete TestDB.sys.sysrowsets where rowsetid = 72057594038845440
delete TestDB.sys.sysallocunits where ownerid = 72057594038845440
commit
go
---test
alter database TestDB set offline
alter database TestDB set online
go
dbcc checkdb ('TestDB') -- no error returned
select * from TestDB.dbo.b -- Invalid object name error will be returned
go

Play it for fun in your sandbox only.

John Huang – SQL MCM & MVP, http://www.sqlnotes.info