Making global changes to all servers

• 
  There isn't an easy way to do this via the GUI (yet?).
  
  The settings are stored in the registry, under the HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name> key. Knowing this, you can do any of the following:
  
  - follow the suggestions in this post to change the settings
  
  - prepare a script that uses either the sqbutility or xp_regwrite extended stored procedures to change the required settings. Run this script using a tool that can target multiple servers e.g. SQL Multi Script.
  
  sqbutility is a SQL Backup extended stored procedure to perform miscellaneous maintenance SQL Backup tasks. One of them is to change the SQL Backup settings. To do that for text values, you would run something like e.g.
  

  EXEC master..sqbutility 1040, 'BACKUPFOLDER', 'G:\Backups\'
  

  where the 1st parameter is fixed at 1040, the 2nd parameter is the name of the setting as found in the registry, and the 3rd parameter the value.
  
  To change number values, use 1041 for the 1st parameter e.g.
  

  EXEC master..sqbutility 1041, 'LogDeleteHours', 24
  

  petey October 29, 2007 19:53

  - 0 +
• 
  Thanks, I'll start looking into that.
  
  Is this a planned change in an upcoming version?

  Adam.Bean October 29, 2007 20:07

  - 0 +
• 
  How do you find the first parameter value?
  
  Also, these registry settings are on the target servers, so this still wouldn't allow me to set a default/make changes to all servers at the same time without connecting to the remote registry. This is where that multi script app comes into play correct?
  
  Thanks

  Adam.Bean October 30, 2007 09:41

  - 0 +
• 

  How do you find the first parameter value?

  I guess you're referring to the sqbutility extended stored procedure. The first parameter is fixed i.e. 1040 to change text values, 1041 to change number values. Assuming you were referring to the 2nd parameter instead, its the name of the values see in HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>.
  

  without connecting to the remote registry

  That's true. So basically you have 2 ways of making the change - manipulating the registry directly, or use the extended stored procedures to do it.
  
  SQL Multi Script makes it convenient to run the same script against multiple servers. Or you could just write a shell script to iterate through a list of servers and run the script on each server in turn.

  petey October 30, 2007 10:30

  - 0 +
• 
  Perfect, thanks for the explanation!

  Adam.Bean October 30, 2007 10:32

  - 0 +
• 

  petey wrote:

  The settings are stored in the registry, under the HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name> key.

  
  What about x64 servers?
  
  I see SQLBackup under HKLM\Wow6432Node\RedGate\SQLBackup, but the path ends there ...
  
  I tried on a 32bit server, and it didn't seem to work ...
  
  EXEC master..sqbutility 1040, 'BackupFileName', '<DATABASE>'
  EXEC master..sqbutility 1040, 'SMTPHost ', 'removed'
  EXEC master..sqbutility 1040, 'SMTPSender', 'removed'
  EXEC master..sqbutility 1041, 'LogDeleteHours', 7
  
  0 results, registry remains the same.
  
  Thanks again

  Adam.Bean October 30, 2007 10:43

  - 0 +
• 
  Is this SQL Multi Script going to be integrated into the application any time soon?
  
  Is there anyway to have it read from your registered servers in SQLBU or SSMS? I have quite a few servers that I'm going to want to make several changes to using this tool once I get that procedure working.
  
  Thanks

  Adam.Bean October 30, 2007 10:50

  - 0 +
• 
  Are you connected to the relevant instance before running the extended stored procedure? You cannot connect to instance A and run the extended stored procedure to change the settings for instance B, although they are both on the same server.

  petey October 30, 2007 11:05

  - 0 +
• 

  petey wrote:

  Are you connected to the relevant instance before running the extended stored procedure? You cannot connect to instance A and run the extended stored procedure to change the settings for instance B, although they are both on the same server.

  
  Correct, I am on the target instance attempting to change the settings for that particular instance.

  Adam.Bean October 30, 2007 11:05

  - 0 +
• 
  Does the SQL Backup Agent service startup user have rights to create/edit entries in that registry key? It appears not to be the case. Could you try using the SQL Backup GUI, enter some settings, and see if they get written to the registry?
  
  Thanks.

  petey October 30, 2007 11:16

  - 0 +
• 

  petey wrote:

  Does the SQL Backup Agent service startup user have rights to create/edit entries in that registry key? It appears not to be the case. Could you try using the SQL Backup GUI, enter some settings, and see if they get written to the registry?
  
  Thanks.

  
  Ok, I just had to refresh ...
  
  It does work, but the return from the procedure is nothing. Is this normal?

  Adam.Bean October 30, 2007 11:20

  - 0 +
• 
  Another random small problem ...
  
  If you use the SQL Multi Script to update the email tab (SMTPHost, SMTPSender) and refresh your connection, the tab does not update until you restart the SQL BU application. The file/logging tab works fine on refresh though.

  Adam.Bean October 30, 2007 11:33

  - 0 +
• 
  That is normal. If you want feedback, you could:
  
  - use the 4th parameter, which is an output parameter. 1 indicates success, 0 indicates failure, -1 indicates an invalid setting name. E.g.
  

  DECLARE @result INT
  EXEC master..sqbutility 1040, 'SMTPHost', 'smtp.myco.com', @result OUTPUT
  SELECT @result
  

  
  - update the setting, then retrieve the value to check if its updated. This is done via sqbutility again, but with function number 1014. E.g.
  

  DECLARE @result NVARCHAR(256)
  EXEC master..sqbutility 1014, 'SMTPHost', @result OUTPUT
  SELECT @result
  

  
  - update a bunch of settings, then retrieve all settings and check that they have been upated, using sqbtutility with function number 1008. E.g.
  

  DECLARE @result NVARCHAR(2048)
  EXEC master..sqbutility 1008, @result OUTPUT
  SELECT @result
  

  petey October 30, 2007 11:36

  - 0 +
• 
  Ah ok, thanks yet again.

  Adam.Bean October 30, 2007 11:38

  - 0 +
• 
  It would be very good if you could save a master list of servers/credentials in the sql multi script tool and then select from this master list which servers to execute the scripts against thus preventing the need to re-enter credentials each time.

  nephila November 02, 2007 16:16

  - 0 +

Add comment

Please sign in to leave a comment.