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,
…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!