May 072013
 

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

 

 

Share/Bookmark

  12 Responses to “Attach Database with Missing NDF File”

  1. Thank you for sharing this valuable article.. Its realy very interesting.

  2. 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.

    • Yes, this method works for version 2008 and 2012. But never tested on 2005.

      • Works well on my test instance. I was concerned, since my case didn’t have a secondary (FG1) file group, but it appears to work on the PRIMARY file group as well.

        I’ll be trying it on my production DB this afternoon, and I’ll let you know how it goes!

      • Works like a champ! Thank you so much.

        –Rob

  3. work great~especially when ndf only contain index

  4. 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…

  5. 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

Add Comment Register



 Leave a Reply

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: