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
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?
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.
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]
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?
Hi,
Please ignore my questions as I have found that I had not deleted the partition schema and function.
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.