Skip to content

view-or-change-the-default-locations-for-data-and-log-files.md

Latest commit

 

History

History
80 lines (60 loc) · 3.42 KB

view-or-change-the-default-locations-for-data-and-log-files.md

File metadata and controls

80 lines (60 loc) · 3.42 KB
title View or Change the Default Locations for Data and Log Files
description Find out how to view or change the default locations for SQL Server data files and log files. See how to protect the files with access control lists (ACLs).
author rwestMSFT
ms.author randolphwest
ms.date 08/26/2025
ms.service sql
ms.subservice configuration
ms.topic how-to
ms.custom
linux-related-content
helpviewer_keywords
log files [SQL Server], changing default location
data files [SQL Server], changing default location

View or change the default locations for data and log files

[!INCLUDE sql-windows-only]

The best practice for protecting your data files and log files is to ensure that they are protected by access control lists (ACLs). Set the ACLs on the directory root under which the files are created.

Note

These instructions are for [!INCLUDE ssnoversion-md] on Windows only. To change the default locations for [!INCLUDE sqlonlinux-md], see Configure SQL Server on Linux with the mssql-conf tool.

Use SQL Server Management Studio

  1. In Object Explorer, right-click on your server and select Properties.

  2. In the left panel on that Properties page, select the Database settings tab.

  3. In Database default locations, view the current default locations for new data files and new log files. To change a default location, enter a new default pathname in the Data or Log field, or select the browse button to find and select a pathname.

  4. After changing the default locations, you must stop and start the [!INCLUDE ssnoversion-md] service to complete the change.

Use Transact-SQL

Caution

The following example uses an extended stored procedure to modify the server registry. Serious problems might occur if you modify the registry incorrectly. These problems might require you to reinstall the operating system. Microsoft can't guarantee that these problems can be resolved. Modify the registry at your own risk.

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window. Replace the <path_*> values with the new locations you wish to place your data and log files, and then select Execute.

    USE [master];
GO

EXECUTE xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'BackupDirectory',
    REG_SZ,
    N'<path_to_database_backup_files>';
GO

EXECUTE xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData',
    REG_SZ,
    N'<path_to_data_files>';
GO

EXECUTE xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog',
    REG_SZ,
    N'<path_to_log_files>';
GO

  4. After changing the default locations, you must stop and start the [!INCLUDE ssnoversion-md] service to complete the change.

Related content