Posts Tagged ‘How to Change the Default SQL Server backup folder’

By default, SQL Server takes the backup in the following location (‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\’). But if you don’t take the backup in this location, each time you need to change the backup path.To avoid this, you can set your default database backup path.

Here is the solution :
You can change the default backup path from the given below script.

DECLARE @HkeyLocal nvarchar(18)
DECLARE @BackupDirectory nvarchar(512)
DECLARE @InstanceRegPath sysname
DECLARE @MSSqlServerRegPath nvarchar(31)
DECLARE @NewPath nvarchar(100)

SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
SET @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

EXEC xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT  
SELECT @BackupDirectory -- Read the default backup directory path

-- Update/Change the default backup directory path to @NewPath
SET @NewPath='D:\DBBackup' -- Change the new default backup folder here 
EXEC xp_instance_regwrite @HkeyLocal,@InstanceRegPath, N'BackupDirectory', REG_SZ,@NewPath 

Here is screen image of before processing this script :

Here is the screen image after processing this script :

Your feedback is very much appreciated. Please do write.

Read Full Post »