Attach Database with Missing NDF File

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

 

 

15 thoughts on “Attach Database with Missing NDF File”

  1. You don’t mention specific SQL Server versions (or I missed it if you did)
    I have a similar situation with a 2012 instance — large MDF file for my database, and I added a second file to the file group, but the second file was never used.
    We lost the drive that housed the second file, and I don’t have a backup at the OS level nor a database backup.

    Do you think this method will work with SQL2012?
    I’ll try to replicate the problem on a test DB today.

    Reply
  2. Thanks for the article..

    it found really helpful when i lost ndf file after reconfiguring new server.. we are hopless and it helped to revert back over database…

    Thanks…

    Reply
  3. Hi John,

    Thank you for great articles you write on SQL server.
    I have question on moving .ndf files

    Say, I have database(DB1) on server(server1), which has 3 FileGroups(FG1,FG2,FG3) along with .MDF and .LDF files.
    Now I have another database(DB2) on another server(server2), I would like to copy the .NDF files from server1 to server2, and do a simple command like.

    CREATE DATABASE …. FOR ATTACHMENT.

    Do you think this is possible?

    Thank you
    Nik

    Reply
  4. Dear John,

    I don’t know if this article really fits my situation. In my case DataBase files say .mdf, .log everything is missing from the directory. I have no clue what happened with this, and database from SQL Server management studio is showing database-name(Recovery pending). can you please help me to find out the exact reason behind this. I have a doubt that if someone else is knowingly deleted those database files or some problem with system or some virus.

    It would be great if you help on this.

    Regards
    Anand kumar Singh

    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.