How to attach a database to a lost or damaged NDF File

 

In the forum, we can see that the NDF file is lost and there is no backup, so we cannot attach the database. I have also seen many answers on the Internet that the append fails without NDF.

 

In fact, I have tested it myself, even if there is no NDF, it can be attached successfully. However, if possible, the lost NDF file does not belong to the primary file group and the SQL Server is the Enterprise Edition.

 

The following is my test:

 

1. -- create a database

 

Create Database [test1] Containment = none
On Primary

(Name = n 'test1', filename =
N 'C: \ data \ test1.mdf ', size
= 5120kb, maxsize
= Unlimited, filegrowth = 1024kb ),

Filegroup
[New]

(Name = n' new', filename =
N 'C: \ data \ new. ndf ',
Size = 5120kb,
Maxsize =
Unlimited, filegrowth = 1024kb)

Log
On

(Name = n' test1 _ log', filename =
N 'C: \ data \ test1_log.ldf', size
= 1024kb, maxsize
= 2048 GB, filegrowth
= 10%)

Go

 

2. -- create two tables on the primary file group

 

Create tabletest (namevarchar (10) on [primary]

Create tabletest2 (namevarchar (10) on [primary]

-- Create a table on the NDF

Create tabletest1 (namevarchar (10) onnew

 

3. -- Insert 10 data records

 

Insert into testvalues
('Kevin ')

Go 10

Insert into test1values
('Kevin ')

Go 10

Insert into test2values
('Kevin ')

Go 10

 

4. -- detach Database

Use [Master]

Go

Exec master. DBO. sp_detach_db @ dbname = n 'test1'

Go

 

5. Copy the MDF, NDF, and LDF files to other folders.

 

6. Follow the steps in step 1 to create a database with the same name

 

-- Offline database

Use master

Go

Alter databasetest1set
Offline

 

7. overwrite the MDF and LDF files backed up in step 1 to the existing database MDF and LDF.

 

8. Run the problematic NDF file offline.

 

Alter databasetest1modify
File (name = new
, Offline)

 

9. Online

 

Alter databasetest1set
Online

 

Now we can see that all three tables exist.

 

 

However, when accessing the test1 table, the following error occurs:

 

MSG 8653, level 16, state 1, line 1

The queryprocessor is unable to produce a plan for the table or view 'test1' because thetable resides in a filegroup which is not online.

 

Because the filegroup of the table is offline, it cannot be accessed.