Use dbcc checkprimaryfile to query Detach database information

In the forum, I encountered a problem where I could not Attach the database. The error message is:

 

Msg 5171, Level 16, State 1, Line2 "path \ allen_log.ldfcm not a primary database file.

 

Obviously, this error indicates that the file selected by the Attch database is not the master database file, but the attached data file does end with MDF. Ask if the user has multiple MDF files. The user says yes, but he is sure that the selected file must be the primary data file and there is no error. He thinks the database file is corrupted. I'm sure this error is caused by the incorrect primary data file.

 

Use Profiler Trace to capture the script and run the following statement in the background:

 

Declare @ command nvarchar (300)

Select @ command = 'dbcc checkprimaryfile (n''' + @ fileName + ''', 2 )'

Create table # smoPrimaryFileProp (propertysql_variantNULL, valuesql_variantNULL)

Insert # smoPrimaryFileProp exec (@ command)

 

The 'dbcc checkprimaryfile is used in it. You can check the dbcc command on the Internet to query the master data file information of the Detach database. You can use the following four options:

 

DBCCCHECKPRIMARYFILE ('d: \ Program Files \ Microsoft SQLServer \ MSSQL10_50.R2 \ MSSQL \ DATA \ backup \ allen. mdf ', 0)

GO

DBCCCHECKPRIMARYFILE ('d: \ Program Files \ Microsoft SQLServer \ MSSQL10_50.R2 \ MSSQL \ DATA \ backup \ allen. mdf ', 1)

GO

DBCCCHECKPRIMARYFILE ('d: \ Program Files \ Microsoft SQLServer \ MSSQL10_50.R2 \ MSSQL \ DATA \ backup \ allen. mdf ', 2)

GO

DBCCCHECKPRIMARYFILE ('d: \ Program Files \ Microsoft SQLServer \ MSSQL10_50.R2 \ MSSQL \ DATA \ backup \ allen. mdf ', 3)

 

650) this. width = 650; "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1SR91R5-0.png "/>

 

The result is as follows:

0 indicates whether the file is an MDF file.

1. The most detailed query can be performed on the database file size growth File ID.

2. The database name is displayed. The internal version number has been sorted.

3 is the result of 1 reduction

 

The preceding error is reported when DBCC checkprimaryfile is used to query the file in the attachment. Therefore, make sure that the MDF file is incorrect and you can use other files to correctly append the file.

 

In fact, there is no need to solve this problem so much trouble. Just try a few MDF files. However, after knowing this command, it is very easy to query the Detach database information in the future.

 

In addition, it is very important to have a good naming convention. Do not name all auxiliary files as MDF. You should set the rules for NDF or yourself.

This article is from the "follow SQL Server Technology" blog, please be sure to keep this source http://lzf328.blog.51cto.com/1196996/1119452