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!

Keep up to date with Simple-Talk

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

This post has been viewed 2610 times – thanks for reading.

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

Phil Factor

Follow on

View all articles by Phil Factor