Click here to monitor SSC
A service from Red Gate

in
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Phil Factor's Phrenetic Phough... > Twiddling the knobs: The Default Backup directory

Phil Factor's Phrenetic Phoughts

Simple-Talk columnist
The wilder shores of Transact SQL    Phil on Twitter   Phil on SQL Server Central  Phil on BOS

Twiddling the knobs: The Default Backup directory

Published Thursday, March 22, 2007 12:18 PM

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.

CREATE PROCEDURE [dbo].[spDMODefaultBackupDirectory] (

@SourceServer VARCHAR(30),

@SourceUID VARCHAR(30)=NULL,

@SourcePWD VARCHAR(30)=NULL,

@toName VARCHAR(100) = NULL

)

AS

/*

spDMODefaultBackupDirectory 'MyServer'--see what the default is

spDMODefaultBackupDirectory 'MyServer', 'myuserid', 'mypassword'

                       --see what the default is

spDMODefaultBackupDirectory 'MyServer', @toName='g:\DatabaseBackups'

*/

DECLARE @objServer INT,

@strErrorMessage VARCHAR(255),

@objErrorObject INT,

@objRegistry INT,

@hr INT,

@DefaultBackupDirectory VARCHAR(255)



-- Sets the server to the local server

IF @SourceServer IS NULL SELECT @SourceServer = @@servername



SET NOCOUNT ON



SELECT @strErrorMessage = 'instantiating the DMO',

       @objErrorObject=@objServer

EXEC @hr= sp_OACreate 'SQLDMO.SQLServer2', @objServer OUT



IF @SourcePWD IS NULL OR @SourceUID IS NULL

       BEGIN

       --use a trusted connection 

       IF @hr=0 SELECT @strErrorMessage=

       'Setting login to windows authentication on '

           +@SourceServer, @objErrorObject=@objServer

       IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1

       IF @hr=0 SELECT @strErrorMessage=

       'logging in to the requested server using windows authentication on '

           +@SourceServer

       IF @SourceUID IS NULL AND @hr=0

        EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL, @SourceServer 

       IF @SourceUID IS NOT NULL AND @hr=0 

           EXEC @hr=sp_OAMethod 

               @objServer, 'Connect', NULL, @SourceServer ,@SourceUID

       END

ELSE

       BEGIN

       IF @hr=0 SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+

           ''' with user ID '''+@SourceUID+'''', @objErrorObject=@objServer

       IF @hr=0 

           EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL, 

               @SourceServer, @SourceUID, @SourcePWD

       END



IF @hr=0 SELECT @strErrorMessage = 'accessing the registry', 

       @objErrorObject=@objServer

IF @hr=0 EXEC @hr=sp_OAGetProperty @objServer, 'Registry',

       @objRegistry output

IF @hr=0 SELECT @strErrorMessage = 'Getting the default Backup directory', 

       @objErrorObject=@objRegistry

IF @hr=0 EXEC @hr=sp_OAGetProperty @objRegistry, 'Backupdirectory',

       @DefaultBackupDirectory output

IF @toName IS NOT NULL AND @hr=0

       BEGIN

       IF @hr=0 SELECT @strErrorMessage = 'Changing the backup directory',

        @objErrorObject=@objRegistry

       IF @hr=0 EXEC @hr=sp_OASetProperty @objRegistry, 'Backupdirectory',

        @toName

       END

IF @hr=0 SELECT @strErrorMessage = 'disconnecting from the server', 

       @objErrorObject=@objServer

IF @hr=0 EXEC @hr=sp_OAMethod @objServer, 'DisConnect'

IF @hr=0 SELECT @DefaultBackupDirectory, COALESCE(@toName,'not changed')

IF @hr<>0

       BEGIN

       DECLARE 

           @Source VARCHAR(255),

           @Description VARCHAR(255),

           @Helpfile VARCHAR(255),

           @HelpID INT

       

       EXECUTE sp_OAGetErrorInfo  @objErrorObject, 

           @source output,@Description output,@Helpfile output,@HelpID output

       SELECT @hr, @source, @Description,@Helpfile,@HelpID output

       SELECT @strErrorMessage='Error whilst '

               +COALESCE(@strErrorMessage,'doing something')

               +', '+COALESCE(@Description,'')

       RAISERROR (@strErrorMessage,16,1)

       END

EXEC sp_OADestroy @objServer

RETURN @hr

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!

by Phil Factor

Comments

 

Haywood said:

Looks like xp_regwrite has been officially neutered in 2005....

This should work, but fails to make the update to the registry key.  If I update it by hand manually, the value takes.  I can only assume (as I'm still looking for doc's) that security has been applied to prevent malicious activity (IIRC, I read somewhere that there was to be some serious restrictions to the access SQL Server has to it's own registry keys even)...

I'm not even going to try a DMO script since yours doesn't work either...


[CODE]
SET NOCOUNT ON
GO
-- Get instance name:
DECLARE @IsDefault varchar(255)
, @DataPath varchar(255)
, @DynExec varchar(max)
, @DynWrite varchar(max)
, @NewBackupPath varchar(255)

SET @NewBackupPath = 'C:\MSSQL\Backup'

SET @IsDefault = (SELECT CONVERT(VARCHAR(75),SERVERPROPERTY('InstanceName')))

IF @IsDefault IS NULL
BEGIN
SET @IsDefault = 'MSSQLSERVER'
END


-- Use instance name to find out install path.
DECLARE @Instance TABLE ([Value] varchar(255), [Data] varchar(255))
INSERT INTO @Instance
EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
, @value_name = @IsDefault


SET @DataPath = (SELECT [Data] FROM @Instance)

-- Read current BackupDir (just to have the info)
SET @DynExec = ('master..xp_regread @rootkey = ''HKEY_LOCAL_MACHINE''
, @key = ''SOFTWARE\Microsoft\Microsoft SQL Server\' + @DataPath + '\MSSQLSERVER''
, @value_name = ''BackupDirectory'' ')

EXEC (@DynExec)


-- Set new value:
SET @DynWrite = ('master..xp_regwrite @rootkey = ''HKEY_LOCAL_MACHINE''
, @key = ''SOFTWARE\Microsoft SQL Server\' + @DataPath + '\MSSQLSERVER''
, @value_name = ''BackupDirectory''
, @type = ''REG_SZ''
, @value = ''' + @NewBackupPath + ''' ')
PRINT ''
PRINT @DynWrite
PRINT ''

EXEC (@DynWrite)
[/CODE]
March 23, 2007 2:07 PM
 

Phil Factor said:

The DMO script works fine on my versions of SQL Server 2005, and on SQL Server 2000.  Your script works fine too. The only thing I noticed was that the @Value parameter was an output variable on xp_regread, so I changed the code a bit. I'm not sure why your code doesn't work on your server, though I suspect it is the surface area configuration.
(note to any casual reader- we are doing wicked reckless things, so please avert your gaze)

SET NOCOUNT ON
GO
-- Get instance name:
DECLARE @IsDefault varchar(255)
, @DataPath varchar(255)
, @NewBackupPath varchar(255)
, @CurrentValue varchar(2000)
, @OurKey varchar (2000)

SET @NewBackupPath = 'C:\MSSQL\Backup'

select @isDefault=coalesce(
(CONVERT(VARCHAR(75),SERVERPROPERTY('InstanceName'))),
'MSSQLSERVER'
)

-- Use instance name to find out install path.

EXEC master.sys.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
, @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
, @value_name = @IsDefault
, @value=@Datapath output

-- Read current BackupDir (just to have the info)
Select @ourKey='SOFTWARE\Microsoft\Microsoft SQL Server\'
+@Datapath+'\MSSQLSERVER'

EXECUTE master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
        @key=@ourKey,
        @value_name='BackupDirectory',
        @value=@CurrentValue OUTPUT
Select @CurrentValue

Execute master..xp_regwrite @rootkey = 'HKEY_LOCAL_MACHINE',
       @key=@ourKey,
       @value_name='BackupDirectory',
       @type = 'REG_SZ',
       @value = @NewBackupPath

EXECUTE master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
       @key=@ourKey,
       @value_name='BackupDirectory',
       @value=@CurrentValue OUTPUT

Select @CurrentValue

March 24, 2007 1:27 PM
 

The SQL Server Thought Police said:

You're surely not advocating writing to the registry on your Servers are you?
March 25, 2007 8:43 AM
 

Haywood said:

You betcha!  If I can do it progromatically and safely (key word) I sure will do it on some of my servers.

I make use of values such as this (backup directory) for my administrative code that 'runs' a server.  It's much better to be able to do it progromatically and repeatably than to do it by hand, IMO.  That, and I'm too lazy to want to do it by hand on each and every server that needs it...
March 25, 2007 9:26 AM
 

The SQL Server Thought Police said:

Oh, that's all right then. Mind how you go Sir.
March 25, 2007 3:28 PM
 

WebMister said:

I don't think any of this is actually necessary since the Server.Settings.BackupDirectory setting in SMO is  'Get' and 'Set', according to MSDN. All you need to do is to Glue together a C# or VB application to use SMO and write a whole lot of code and.....  ..er.. OK, you may have a point.

I believe you can also read and write to the registry using the WSCRIPT.SHELL object and using the RegRead() and RegWrite methods. But you'll need Haywood's nifty way of finding the instance. It will get around any of Microsoft's security restrictions. I'm sure glad the SQL Server Thought Police have left.
March 25, 2007 3:49 PM
 

viacoboni said:

For what its worth, here's my solution in Perl for addressing several registry options.  I plan to do an article about it when I get time.  

Usage:
   perl SetSqlRegKeys.pl instance BackupDirectory
       (Reports current BackupDirectory location.)
   perl SetSqlRegKeys.pl instance BackupDirectory E:\Backup
       (Sets BackupDirectory registry key to E:\Backup.)

[CODE]
use strict;
use Win32::TieRegistry (Delimiter => '/');

my %RegKeys = ('BackupDirectory'       => '/MSSQLServer//BackupDirectory',
              'Port'                  => '/MSSQLServer/SuperSocketNetLib/Tcp/IPAll//TcpPort',
              'DynamicPorts'          => '/MSSQLServer/SuperSocketNetLib/Tcp/IPAll//TcpDynamicPorts',
              'FullTextDefaultPath'   => '/MSSQLServer//FullTextDefaultPath',
              'ErrorLog'              => '/MSSQLServer/Parameters//SqlArg1',
              'MasterDataFile'        => '/MSSQLServer/Parameters//SqlArg0',
              'MasterLogFile'         => '/MSSQLServer/Parameters//SqlArg2',
              'CurrentVersion'        => '/MSSQLServer/CurrentVersion//CurrentVersion',
              'RegisteredOwner'       => '/MSSQLServer/CurrentVersion//RegisteredOwner',
              'SerialNumber'          => '/MSSQLServer/CurrentVersion//SerialNumber',
              'Collation'             => '/Setup//Collation',
              'Edition'               => '/Setup//Edition',      
              'PatchLevel'            => '/Setup//PatchLevel',  
              'SQLBinRoot'            => '/Setup//SQLBinRoot',  
              'SQLDataRoot'           => '/Setup//SQLDataRoot',  
              'SQLPath'               => '/Setup//SQLPath',      
              'SQLProgramDir'         => '/Setup//SQLProgramDir',
              'Version'               => '/Setup//Version',
              'AgentWorkingDirectory' => '/SQLServerAgent//WorkingDirectory',
              'AgentErrorLogFile'     => '/SQLServerAgent//ErrorLogFile',
             );


my $inst = shift;
my $parm = shift;
my $parmval = shift;

syntax() unless ($inst);

my $sqlkey = $Registry->{"LMachine/SOFTWARE/Microsoft/Microsoft SQL Server"};
my $InstNamesKey = $sqlkey -> {"Instance Names/SQL/"};
my @parms;
my @Instances;

if (!$parm or $parm eq '*' or $parm =~ /^all$/i) {
   die "Can't set a value for all registry keys." if ($parmval);
   @parms = sort keys %RegKeys;
} else {
   unless (exists($RegKeys{$parm})) {
       # Since the hash is case-sensitive, search for a case-insensitive match
       foreach my $key (keys %RegKeys) {
           if (lc $key eq lc $parm) {
               $parm = $key;
               last;
           }
       }
       unless (exists($RegKeys{$parm})) {
           print "$parm is not a valid parameter - see syntax below.\n\n";
           syntax();
       }
   }
   @parms = ( $parm );
}

if ($inst eq '*' or $inst =~ /^all$/i) {
   @Instances = $InstNamesKey->ValueNames;
} else {
   die "$inst not found as SQL instance.\n" unless ($InstNamesKey -> GetValue($inst));
   @Instances = ( $inst );
}

foreach $inst (@Instances) {
   foreach $parm (@parms) {
       getparmval($inst, $parm);
       setparmval($inst, $parm, $parmval) if ($parmval);
   }
}

sub getparmval {
   my ($inst, $parm) = @_;
   my $DirName = $InstNamesKey -> GetValue($inst);
   die "$parm registry key $RegKeys{$parm} not found.\n" unless (exists $sqlkey -> {"$DirName$RegKeys{$parm}"});
   my $regval = $sqlkey -> {"$DirName$RegKeys{$parm}"};
   print "$inst ($DirName) $parm = $regval.\n";
}

sub setparmval {
   my ($inst, $parm, $parmval) = @_;
   my $DirName = $InstNamesKey -> GetValue($inst) or die "$inst not found as SQL instance.\n";
   $parmval = '' if ($parmval eq '(empty)' or $parmval eq "''");
   $sqlkey -> {"$DirName$RegKeys{$parm}"} = $parmval;
   print "\t$parm set to $parmval.\n";
}


sub syntax {
   my $parms = join("\n\t", sort keys(%RegKeys));
   [postbody] =~ s/^.*\\(.*)$/$1/;
print << "DONE";
[postbody] - Retrieve and Set Instance-specific SQL Server registry keys
syntax: perl [postbody] instance parm [new value]
where
   instance = name of SQL instance (* or all uses all instances)
   parm = one of the following (* or all allowed display only):\n\t$parms
   new value = optional value to set key (use '' to set to empty).
DONE
   exit(0);
}
[/CODE]
       
Vince
April 13, 2007 12:34 PM
 

Phil Factor said:

Vince, it will be a fascinating article. I hope we get it at Simple-Talk!
April 13, 2007 12:38 PM
 

The SQL Server Thought Police said:

SQL Server programming in Perl! Whatever next?
April 14, 2007 5:55 AM
 

r l reid said:

Yes, SQL Server programming in Perl - some of us do it all the time.

Pick up a copy of "Real World SQL Server Administration with Perl" by Microsoft MVP Linchi Shea.   His motto: "Automation Leads to Relaxation"!

For those of us who were not raised in the George Jetson "have the human push buttons" world, being able to script non-trivial jobs is how we have time to sleep.

That's why the DBI modules and things like SQLFairy exist, and of course
FreeTDS (well, FreeTDS exists for other reasons, but it also makes it possible to compile (pardon the expression) DBI::Sybase in a platform independant way: tell it the TDS level for the connection, and speak the right SQL dialect and it doesn't care if it's an MS Server.

I mean, how else do you guys sync up data between Sybase, SQL Server, MySQL, and the like?   How else do you run jobs on MS SQL Server from non-Windows platforms?   There's better stuff for 2005 than in the past, but you still can't migrate from Sybase to SQL Server using Microsoft tools, not if you have 125 databases, several million lines of code in C, VB, Java, JavaScript, C#, SQL written before ANSI standards,

April 17, 2007 3:07 PM
 

Phil Factor said:

... and Perl is one of the few languages that exist in a consensed and standard form across platforms. I'm a great fan of ActivePerl, ( http://www.activestate.com/products/activeperl/ )and it is amazing what can be achieved with it. I have to admit, however, I've converted to PHP5 for most jobs just because of the sheer convenience and code-base of the latter. Come on somebody, what about an article for Simple-Talk on using Perl and PerlNET with SQL databases and .net?
April 18, 2007 4:03 AM
You need to sign in to comment on this blog
<March 2007>
SuMoTuWeThFrSa
25262728123
45678910
11121314151617
18192021222324
25262728293031
1234567
Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL... Read more...

Converting String Data to XML and XML to String Data
 We all appreciate that, in general, XML documents or fragments are held in strings as text markup. In... Read more...

Geek of the Week: Don Syme
 With the arrival of F# 3.0 Microsoft announced a wide range of improvements such as type providers that... Read more...

How to Document and Configure SQL Server Instance Settings
 Occasionally, when you install identical databases on two different SQL Server instances, they will... Read more...

What's the Point of Using VARCHAR(n) Anymore?
 The arrival of the (MAX) data types in SQL Server 2005 were one of the most popular feature for the... Read more...