Using Policy Based Management for checking SQL Server database file extensions

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

Problem

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?

Solution

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...":
  
  
• 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:
  
  
  
  
• 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:
  
  
  
  
• Click "OK"

Create Policies

• In SSMS go to Management > Policy Management > Policies:
• Right click "Policies", then click "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:
  
  
  
  
• 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".

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:
   

  ALTER DATABASE MSSQLTips_DemoDB MODIFY FILE 
    (NAME = MSSQLTips_DemoDB_log, 
     FILENAME='E:\MSSQL2005\MSSQL.1\MSSQL\MSSQLTips_DemoDB_log.ldf')
  

• 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

About the author

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