Click here to monitor SSC

Simple-Talk columnist

Twiddling the knobs: The Default Backup directory

Published 22 March 2007 6:18 am

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!

11 Responses to “Twiddling the knobs: The Default Backup directory”

  1. Haywood says:

    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:MSSQLBackup’

    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 = ‘SOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL’
    , @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 = ”SOFTWAREMicrosoftMicrosoft SQL Server’ + @DataPath + ‘MSSQLSERVER”
    , @value_name = ”BackupDirectory” ‘)

    EXEC (@DynExec)

    – Set new value:
    SET @DynWrite = (‘master..xp_regwrite @rootkey = ”HKEY_LOCAL_MACHINE”
    , @key = ”SOFTWAREMicrosoft SQL Server’ + @DataPath + ‘MSSQLSERVER”
    , @value_name = ”BackupDirectory”
    , @type = ”REG_SZ”
    , @value = ”’ + @NewBackupPath + ”’ ‘)
    PRINT ”
    PRINT @DynWrite
    PRINT ”

    EXEC (@DynWrite)
    [/CODE]

  2. Phil Factor says:

    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:MSSQLBackup’

    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 = ‘SOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL’
    , @value_name = @IsDefault
    , @value=@Datapath output

    – Read current BackupDir (just to have the info)
    Select @ourKey=’SOFTWAREMicrosoftMicrosoft 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

  3. The SQL Server Thought Police says:

    You’re surely not advocating writing to the registry on your Servers are you?

  4. Haywood says:

    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…

  5. The SQL Server Thought Police says:

    Oh, that’s all right then. Mind how you go Sir.

  6. WebMister says:

    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.

  7. Anonymous says:

    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.nn”;
               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(“nt”, 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):nt$parms
       new value = optional value to set key (use ” to set to empty).
    DONE
       exit(0);
    }
    [/CODE]
           
    Vince

  8. Phil Factor says:

    Vince, it will be a fascinating article. I hope we get it at Simple-Talk!

  9. The SQL Server Thought Police says:

    SQL Server programming in Perl! Whatever next?

  10. Anonymous says:

    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,

  11. Phil Factor says:

    … 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?

Leave a Reply