Bring Offline File Online — Modify SQL Server Metadata

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

6 thoughts on “Bring Offline File Online — Modify SQL Server Metadata”

  1. John:

    I just want to clarify your comments from the first paragraph above “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.”

    Are you saying that you cannot update the sys.sysbrickfiles in version 2008 to bring offline or defunct files online?

    Reply
    • Hi Lawrence, you are right, in SQL Server 2008 and up, Modifying those tables are not allowed unless bringing sql server to single user mode, as demonstrated here.

      Reply
  2. you can try this method
    USE master
    go

    –restore file
    RESTORE DATABASE [TestDB] FILE=’TestDB_File1′ WITH RECOVERY

    USE TestDB
    go
    SELECT * from sys.[database_files]

    USE TestDB
    go
    select * FROM [dbo].[b]

    Reply
  3. Hi,

    I followed the steps described above but had to include
    DELETE from sys.sysschobjs where pid = 1185881229 — parent_id
    and
    DELETE from sys.sysrowsetrefswhere objid= 1185881229

    We did not update the status of the sys.sysbrickfiles as we do not have the files and they are in RECOVERY_PENDING or DEFUNCT mode.
    And if we change to status to online it leaves the database in ‘Recovery Pending’ mode.

    Now whenever we run CHECKDB and CHECKCATALOG we get missing references in sys.sql_expression_dependencies. I have searched to see what is system table behind this object but with no luck. Do you know the system table I have to update?

    Also, when running CHECKDB we get an Index Allocation Map error given us the partition Id for each error. Do you know how I can fix this problem?

    Reply
  4. Life saver!!!

    I would like to point out that in our environment (SQL 2008) the sys.sysbrickfiles view was not there. Once we upgraded the SQL server to ver 2008 R2 the view was there and we were able to set the file back to online and recover all the data.

    Thanks for posting these instructions.

    Reply

Leave a Comment

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.