Skip to content

Latest commit

 

History

History
75 lines (58 loc) · 3.39 KB

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

File metadata and controls

75 lines (58 loc) · 3.39 KB
title description author ms.author ms.date ms.service ms.subservice ms.custom ms.topic helpviewer_keywords
View or change the default locations for data and log files
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).
rwestMSFT
randolphwest
03/22/2024
sql
configuration
linux-related-content
conceptual
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 cannot 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
EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'BackupDirectory',
    REG_SZ,
    N'<path_to_database_backup_files>'
GO
EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData',
    REG_SZ,
    N'<path_to_data_files>'
GO
EXEC 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