Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using Policy Based Management for checking SQL Server database file extensions

MSSQLTips author Svetlana Golovko By:   |   Read Comments (3)   |   Related Tips: More > 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...":
    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:

    Policy
  • 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:
     
    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


Last Update: 4/1/2013


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.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, October 04, 2014 - 12:46:53 AM - JJ Read The Tip

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 Read The Tip

JJ,

 

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 02, 2014 - 8:19:55 PM - JJ Read The Tip

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;

'e:\mssql\cms\data\templog.ldf'

 

@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.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.