(Quickie post #3 while it's Kimberly's turn to lecture this morning - better pay attention now before I get into trouble)
Gail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again - will it work for a detached database with multiple data files in the primary filegroup where one of the secondary data files has a corrupt file header page?
Well, based on my experiences and investigations of file header corruption (see my previous post here) my initial reaction was to say "it will attach but you won't be able to access the database". However, I'd like to try it first - so I'm trying it while writing this post. I've taken the DemoSuspect and added a file, then detached the database and corrupted the first page in the ndf file using my trusty hex editor. Trying to attach the database again gives:
EXEC
sp_attach_db @dbname = N'DemoSuspect',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_log.ldf',
@filename3 = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.ndf';
GO
Msg 5172, Level 16, State 15, Line 1
The header for file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.ndf' is not a valid database file header. The PageAudit property is incorrect.
Ok - just what I expected. Now I'll copy off the files, create the dummy database, shutdown the server, swap in the corrupt files, and restart the server. So what happens?
USE
DemoSuspect;
GO
Msg 945, Level 14, State 2, Line 1
Database 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
SELECT
DATABASEPROPERTYEX ('DemoSuspect', 'STATUS') AS Status;
GO
Status
--------
SUSPECT
ALTER
DATABASE DemoSuspect SET EMERGENCY;
GO
Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.ndf' is not a valid database file header. The PageAudit property is incorrect.
Msg 5120, Level 16, State 9, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.ndf". Operating system error 0: "(null)".
Well, the database gets hacked back into the server, but the corrupt secondary file header trumps everything else - the whole database is unusable because that data file in the primary filegroup is essentially offline. This is because an offline (or inaccessible) file means the filegroup it is part of is offline (or inaccessible) - and an offline primary filegroup means the whole database is offline.
If the secondary file is in a non-primary filegroup, then that filegroup can be manually set offline and the rest of the database is available, in Enterprise Edition - partial database availability.
So, the hack method will work, but whether the database is accessible or not depends on how the database is corrupt in the first place.