“The Only Thing That Is Constant Is Change”

Copy-Create method

Posted by database-wiki on March 21, 2011



We have only the PO_Data.MDF and we need to attach it to an instance to recover datas from a table.

Action Plan:


To find out all NDF and LDF files associated with this primary file. (This will work only if the primary file (mdf) is detached from the instance)

DBCC CHECKPRIMARYFILE (‘D:\PO_Data.mdf’,3) Option 3 should be included to get the desired result.

Status           fileid                name                                 filename


2                     1                          PO_data                          E:\PO_Data.MDF

66                  2                           PO_Log                            L:\PO_Log.LDF

2                    3                           PO_Indexes                   I:\PO_Indexes.NDF

We are missing PO_Log.LDF and PO_indexes.NDF

What is copy-create method?

Create a database with the same logical and physical name as the old database got from the output of DBCC CHECKPRIMARYFILE (‘D:\PO_Data.mdf’,3). Then replace the newly created mdf,ndf,ldf with old mdf,ndf,ldf files.

Using copy-create method we won’t be able to attach the database as the PO_Indexes.NDF is missing, forget about putting the database in single user mode to rebuild the log file.

Lucking old PO_indexes.NDF was available for us.

We know that only thing we need is the NDF file no matter how old it is because the primary PO_Data.MDF file will have the file header information of the old secondary PO_Indexes.NDF file and PO_Indexes.NDF file created using copy-create will have different header information and won’t be accepted by the primary database file PO_Data.MDF while coming online.

So we followed copy create method and replaced new PO_Data.MDF and PO_Indexes.NDF with the old PO_Data.MDF and PO_Indexes.NDF.

Database came up in suspect mode as PO_Log.LDF is missing. Now the plan was to rebuild the transaction log PO_Log.LDF.

To rebuild the transaction log follow the below steps:

1. To rebuild we need to put the database in emergency.

alter database po_sample set emergency

2. To rebuild we need to put the database in single user so that no one access it.

alter database po_sample set single_user

3. Ran below command to rebuild the log file.

alter database po_sample rebuild log on


4. Finally bring the database online.

alter database po_sample set multi_user

After which we were able to bring the po_sample database online.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: