I wonder if you’ve had the situation that I had before where you have to attach a database with one or few missing .ndf files. It seems pretty common since I’ve just seen the same scenario at one of my clients. The reasons behind it are quite similar – They created a new file group with files to host some temporary data for data fixing or testing. New files were not created under the folder as usual and they thought they would remove them right after tasks complete. But the latter part got forgotten afterwards.

Months later, due to various reasons, the database got detached and copied over to another location. Original hard drives were already re-assigned. Backups went to secured place far from the city. DBA then found that one/few small ndf files which contains nothing were not copied over.

It’s not a production database, however, it’s still quite important for other teams, such as development and QA. Retrieving backup will take more than a week. You know that the least important missing parts of the database preventing your to attach the database and let your teams to use the most important parts.

Here is the solution allowing you quickly mount the database with missing NDF files.

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
select * from TestDB.dbo.b
go
use master
go
exec sp_detach_db 'TestDB'
--- delete TestDB_File1.ndf
--- Copy TestDB.mdf and TestDB_log.ldf to elsewhere
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
alter database TestDB modify file(name = 'TestDB_File1', offline)
go
alter database TestDB set offline
-- remove all database files
-- copy TestDB.mdf and TestDB_log.ldf back to C:\Temp
go
alter database TestDB set online
/*
you will receive message below. but it's fine
The Service Broker in database "TestDB" will be disabled because the Service Broker GUID in the database (7DE06CC2-F709-4353-BC17-30A8D141EEFE) does not match the one in sys.databases (9E1BD254-BE20-483A-9E95-ACA98E9009A2).
*/select * from TestDB.dbo.a
/*
id
-----------
1
(1 row(s) affected)
*/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.
*/

The idea behind is that

1. Create a empty database with the save file layout, including names, filegroups, location, etc, everything but data
2. Take the missing files offline
3. Take the database offline
4. Use the data files to overwrite the empty data files
5. Bring the database online

After that, the database will become operational. DBCC checkDB will not return errors. Tables reside in the missing files will not be accessible and drop-able, but you can rename them. Usually such kind of issues happen in none production environment. Removing those unusable tables is not extremely critical in the most of the time.  But if you’d like to make the database clean, SQL Server does allow you to modify the system meta data. I will explain it in my future posts.

John Huang – SQL MCM & MVP, http://www.sqlnotes.info