Using Policy Based Management for checking SQL Server database file extensions

By:   |   Comments (4)   |   Related: > Policy Based Management


We need to exclude SQL Server database files from Antivirus and Third Party Backup Software (to make sure that files are not accessed directly). How can we be sure that all of our SQL Server file extensions are using the standard file extensions for database files?


SQL Server has three general files extensions that are associated with database files:

  • MDF - used by the primary data file
  • NDF - used by all other data files except the primary data file
  • LDF - transaction log file extension

These file extensions are "recommended" as per the "Files and Filegroups Architecture" in Microsoft's article. But technically nothing prevents DBAs or Developers from using other extensions or from making a simple typo.  

One method to check file extensions is Policy Based Management (PBM). If you are new to the PBM you can start by reading this tip: "Using Policy Based Management in SQL Server 2008".

In our tip we will create two policies that will be used for checking file extensions for both data and log files.

Create Conditions

One of the conditions will be used for checking the data files and the other will be used for checking the transaction log files.

Create the condition that will be used to check the data file extensions:

  • In SQL Server Management Studio (SSMS) go to Management > Policy Management > Conditions
  • Right click "Conditions", then click "New Condition...":
    New Condition
  • Enter the name for the condition: "Data Files Extensions"
  • Select "Data File" facet
  • Select @fileName field under "Expression"
  • Add the expression to check if the file's extension is "*.MDF" or "*.NDF" as shown below:

    Condition 1
  • Click "OK"

Create the condition that will be used to check transaction log file extensions:

  • In SSMS go to Management > Policy Management > Conditions
  • Right click "Conditions", then click "New Condition..."
  • Enter the name for the condition: "Log Files Extensions"
  • Select "Log File" facet
  • Select @fileName field under "Expression"
  • Add the expression to check if the file's extension is "*.LDF" as shown below:

    Condition 2
  • Click "OK"

Create Policies

  • In SSMS go to Management > Policy Management > Policies:
  • Right click "Policies", then click "New Policy...":
    New Policy
  • Enter the name for the policy: "Check Data Files Extensions"
  • Select condition "Data Files Extensions" under "Check Condition" as shown below:

  • Click "OK" to save the policy.
  • Similar to the first policy create the second policy named "Check Log Files Extensions" using the "Log Files Extensions" condition:

    Policy 2
  • Click "OK" to save the policy.

Evaluate the Policies

There are several ways to evaluate the policies:

  • You can use Registered Servers in SQL Server Management Studio (SSMS) to evaluate the policy. Refer to this tip for more information.
  • Policies can be evaluated using Central Management Server. Refer to this tip for the details.
  • Another option is to schedule policy evaluation by setting the Execution Mode to "On Schedule". Refer to this tip for an example.
  • There is also an option to evaluate policies using PowerShell and schedule it as a job.

Results for Policy Check

Here is a sample of a policy check.  In this example we can see we are using "mdf" as the extension for a transaction log file which should be "ldf".

Policy evaluated

Make Corrections as Needed

Here is an example of how we can fix this error above.  We are changing the physical file name from "MSSQLTips_DemoDB_log.mdf" to "MSSQLTips_DemoDB_log.ldf".

  • Backup the database that has file naming issues
  • Rename the database file using this script:
      (NAME = MSSQLTips_DemoDB_log, 
  • Take the database offline
  • Rename the physical file in the folder
  • Bring the database back online
Next Steps
  • Check with your Backup Administrator to see if there are backup errors due to any direct file access
  • Evaluate policies on all of your servers using Registered Servers or Central Management Server
  • Rename the files (update the file extensions as needed)
  • Exclude database(s) files from Antivirus
  • Exclude database(s) files from backup (if you use a file level backup agent and backup only SQL Server backup files)
  • Read more tips on Policy Based Management

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 4, 2014 - 11:13:43 AM - Rik T Back To Top (35182)

Looks like PBM does a similar thing to SSIS when using certain characters, you'll need to add in two "\" when evaluating against a DIR, just tried it against 'C:\' which failed then tried it against 'C:\\' and that passed. Same thing if you need to check a DIR within that 'C:\\SomeDIR%'

Saturday, October 4, 2014 - 12:46:53 AM - JJ Back To Top (34834)

I've missed the update, thanks for coming back to me with a soluiton, much appreciated.

Saturday, August 16, 2014 - 1:58:40 PM - Svetlana Golovko Back To Top (34168)



You are right. This doesn't work. I am not sure of this is a Microsoft bug, or if this is just not considered as File name (Path). But you can create your policy using this SQL statement instead of "@FileName" as field:


ExecuteSql('Numeric', 'select count(file_id) from sys.database_files where data_space_id = 1 and physical_name LIKE ''E:\MSSQL%''')

Saturday, August 2, 2014 - 8:19:55 PM - JJ Back To Top (33984)

Any thoughts on why the check of the filepath doesn't work as opposed to the extension?   I've tried to expand on this example and other scenarios don't work.  My tempdb log file is located at;



@FileName LIKE '%.ldf' validates 

@FileName LIKE '%temp%' validates 

@FileName = 'e:\mssql\cms\data\templog.ldf' validates

@FileName LIKE 'e:\mssql\%' doesn't validate 


How come the last one won't?  I wan't to start building policies to check expected file locations.

get free sql tips
agree to terms