2010-08-23

Backup Directory

With SQL Server 2005 we were able to define a default backup directory. This is a nice thing to maintain, so that a restore can be done quickly and correct.
The directory path is stored in the Windows Registry, and is available with a (undocumented) stored procedure.
DECLARE @reg_key_data NVARCHAR(255);
EXECUTE [master].[dbo].[xp_instance_regread]
  N'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer'
 ,N'BackupDirectory'
 ,@reg_key_data OUTPUT;
SELECT @reg_key_data AS N'backup_directory';

(Fig. 1) SQL Server Management Studio, Facets

The script works on SQL server 2005 and 2008.
You could set the backup directory path by the (undocumented) stored procedure master.dbo.xp_instance_regwrite, but I prefer to change the value at the installation or manually using SQL Server Management Studio.
The name of the backup directory is available for read and write in SQL Server Management Studio as a facet to the database instance. Right-click on the instance in Object Explorer and select Facets. In the facet "Server" it is the second item.

Reference

Dinesh Asanka: „Working with the Registry“ (SQLServerCentral).

No comments: