22 March 2007

Twiddling the knobs: The Default Backup directory

Whenever I attempt to set myself up as an expert on anything,  a Higher Being seems to belabour me with a metaphorical baseball bat for my lack of humility. In consequence, this Blog entry is really more of a question than a nugget of information. How does one change the default backup directory for a server by using the SSMS or Enterprise Manager, without changing all the default directories at the same time?. What is the TSQL to do it? (the editor will send a Simple-Talk goodie bag for the best correct solution)

 It is normally set to a value on installation and the value is sitting there smugly in the registry, but I’m BOL’d out. (Can’t find the information on Books-on-line). Here is the way I normally do it…… This routine allows you to see or change the server’s Default Backup Directory.

PS: Gary Sexton has pointed out a link http://sqldev.net./misc/SQLLocationFunctions.htm which should do the trick, but xp_instance_regread and   xp_instance_regaddmultistring,
xp_instance_regdeletekey ,
xp_instance_regdeletevalue ,
xp_instance_regenumkeys ,
xp_instance_regenumvalues ,
xp_instance_regremovemultistring ,
and xp_instance_regwrite
…are all undocumented stored procedures and could easily change from release to release. Many thanks to Gary, but I really wanted a Kosher way of doing it!

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 0    Average: 0/5]

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Follow on

View all articles by Phil Factor