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