top of page

Steps to change the default SQL Server backup directory

This article provides steps to change the default SQL Server backup directory.


By default, when we install SQL Server, it configures a default directory for the SQL Server backups. This directory stores full, differential, and transaction log backup for each executed backup on the SQL Server instance.


To check the default backup location, connect to SQL instance in SQL Server Management Studio and go to Database Settings.


Go to database default locations and verify the backup directory on the page - Database Settings.


The default directory for my SQL Server 2019 instance is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup


SQL Server backup directory

If you want to retrieve the default backup directory from the T-SQL, you can use the XP_instance_regread extended stored procedure.


declare @dir nvarchar(4000) 
exec master.dbo.xp_instance_regread
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'BackupDirectory', 
   @dir output
select @dir

Check default SQL Server backup directory using Registry

It is not advisable to store the backup on the operating system drive. You must change the default backup directory to a sufficient free space drive.

How do we change the backup directory?

You can specify the new backup directory in the database default location section. For example, here, I specify the backup directory to C:\SQL


Modify Backup directory

The equivalent T-SQL script for modifying the default SQL Server backup directory uses the extended stored procedures XP_instance_regwrite.


USE [master]
GO
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'BackupDirectory', 
REG_SZ,
N'C:\SQL'
GO

Now, verify the backup directory using the xp_instance_regread extended stored procedure.

Check modified backup directory

If we execute a database backup, it is stored in the default backup directory unless you specify a different directory.

Start full database backup

Verify backup in the default directory

Comments


bottom of page