修改系统元数据让文件组在线 可以使用还原文件的方法而不是还原文件组
修改系统元数据让文件组在线
http://www.sqlnotes.info/2013/05/09/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
01
use master
02
if db_id('TestDB') is not null
03
drop database TestDB
04
go
05
create database TestDB on PRIMARY ( name = 'TestDB', filename = 'C:\Temp\TestDB.mdf'),
06
filegroup FG1 ( name = 'TestDB_File1', filename = 'C:\Temp\TestDB_File1.ndf')
07
log on ( name = 'TestDB_log', filename = 'C:\Temp\TestDB_log.ldf')
08
GO
09
create table TestDB.dbo.a (id int) on [PRIMARY]
10
insert into TestDB.dbo.a values(1)
11
create table TestDB.dbo.b (id int) on [FG1]
12
insert into TestDB.dbo.b values(2)
13
go
14
select * from TestDB.dbo.a -- return one record id = 1
15
select * from TestDB.dbo.b -- return one record id = 2
16
go
Now let’s turn the file TestDB_File1.ndf offline.
01
alter database TestDB modify file (name = 'TestDB_File1', offline)
02
go
03
--verify the database is offline already.
04
select * from TestDB.dbo.b
05
--Msg 8653, Level 16, State 1, Line 1
06
--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.
07
--This is the expected error message
08
go
09
--bring it back online ?
10
alter database TestDB modify file (name = 'TestDB_File1', online)
11
--Msg 155, Level 15, State 1, Line 1
12
--'online' is not a recognized CREATE/ALTER DATABASE option.
13
go
14
--'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)
01
use master
02
select name, state, state_desc from sys.master_files where database_id = db_id('TestDB')
03
--name state state_desc
04
------------------------ -----------
05
--TestDB 0 ONLINE
06
--TestDB_log 0 ONLINE
07
--TestDB_File1 6 OFFLINE
08
09
--(3 row(s) affected)
10
--Internally, file statuses are saved in sys.master_files system table which is accessed by sys.sysbrickfiles view.
11
select lname, filestate from sys.sysbrickfiles where dbid = db_id('TestDB')
12
--lname filestate
13
-----------------------------
14
--TestDB 0
15
--TestDB_log 0
16
--TestDB_File1 6
17
18
--(3 row(s) affected)
The only thing is to bring the file ONLINE is to modify the filestate of TestDB_File1 to 0.
01
update sys.sysbrickfiles set filestate = 0 where dbid = db_id('TestDB') and lname = 'TestDB_File1'
02
--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.
03
--(1 row(s) affected)
04
05
--Even you've changed the system metadata, the file will not be online immediately. You will have to re-load the database
06
alter database TestDB set offline
07
alter database TestDB set online
08
select * from TestDB.dbo.b
09
--id
10
-------------
11
--2
12
--(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
view sourceprint?
01
use master
02
---get object id and partition id is enough in this case
03
select object_id('TestDB.dbo.b') -- object_id = 2121058592
04
select * from TestDB.sys.sysrowsets where idmajor = 2121058592 -- rowsetid = 72057594038845440
05
06
-- start deleting rows from metadata
07
set xact_abort on
08
begin transaction
09
delete TestDB.sys.sysobjvalues where objid = 2121058592
10
delete TestDB.sys.sysiscols where idmajor = 2121058592
11
delete TestDB.sys.syscolpars where id = 2121058592
12
delete TestDB.sys.sysschobjs where id = 2121058592
13
delete TestDB.sys.sysidxstats where id = 2121058592
14
delete TestDB.sys.syssingleobjrefs where depid = 2121058592
15
delete TestDB.sys.sysrowsets where rowsetid = 72057594038845440
16
delete TestDB.sys.sysallocunits where ownerid = 72057594038845440
17
commit
18
go
19
---test
20
alter database TestDB set offline
21
alter database TestDB set online
22
go
23
dbcc checkdb ('TestDB') -- no error returned
24
select * from TestDB.dbo.b -- Invalid object name error will be returned
25
go
Play it for fun in your sandbox only.
John Huang – SQL MCM & MVP, http://www.sqlnotes.info
elect * FROM [dbo].[b] --消息 8653，级别 16，状态 1，第 1 行 --查询处理器无法为表或视图 'b' 生成计划，因为该表驻留在不处于联机状态的文件组中。 USE master go --指定文件组online不对 --消息 3149，级别 16，状态 5，第 2 行 --文件或文件组 "TestDB_File1" 的状态对要使用的 "Recover Data Only" 选项无效。只能处理处于 OFFLINE 或 RECOVERY_PENDING 状态的辅助文件。 --消息 3013，级别 16，状态 1，第 2 行 --RESTORE DATABASE 正在异常终止。 --文件组会显示为restoring状态 --RESTORE DATABASE TestDB FILEGROUP='FG1' WITH RECOVERY --restore文件 RESTORE DATABASE [TestDB] FILE='TestDB_File1' WITH RECOVERY USE TestDB go SELECT * from sys.[database_files] USE TestDB go select * FROM [dbo].[b]
