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
- Create a empty database with the save file layout, including names, filegroups, location, etc, everything but data
- Take the missing files offline
- Take the database offline
- Use the data files to overwrite the empty data files
- 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
Thank you for sharing this valuable article.. Its realy very interesting.
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
Good to hear that. Cheers
work great~especially when ndf only contain index
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…
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
Hi Nik, answer is no, SQL Server knows if a file belongs to a database or not.
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
Do you still have this problem?
Hello John
Thank you, you saved me 😀
I could not do what you said on http://www.sqlnotes.info/2013/05/09/bring-offline-file-online-modify-sql-server-metadata/ because my file was missing so after forcing the file to online, when doing the set online of DB it failed but this current page was life-saver
Thanks a lot!