Using Policy Based Management for checking SQL Server database file extensions
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.
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:
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,
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
Last Update: 4/1/2013
About the author
Comments and Feedback: