Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

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

 

 

Share/Bookmark

Comments

Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...