Muokkaa

Jaa

View or change the default locations for data and log files

  • Artikkeli

Applies to: SQL Server - 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 SQL Server on Windows only. To change the default locations for SQL Server on Linux, 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 SQL Server 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 Database Engine.

  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 SQL Server service to complete the change.

Related content