What are the challenges you face when working across database platforms? Take the survey
Options

Making global changes to all servers

Adam.BeanAdam.Bean Posts: 28
edited November 2, 2007 4:16PM in SQL Backup Previous Versions
I'm finding it very tedious to have to set server settings (default file format) and email settings for each and every server/instance.

Is there any easy to make the same settings for all registered servers?

Thanks again!

Comments

  • Options
    peteypetey Posts: 2,358 New member
    edited October 29, 2007 9:40PM
    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
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks, I'll start looking into that.

    Is this a planned change in an upcoming version?
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Perfect, thanks for the explanation!
  • Options
    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
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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?
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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&#40;256&#41;
    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&#40;2048&#41;
    EXEC master..sqbutility 1008, @result OUTPUT
    SELECT @result
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Ah ok, thanks yet again.
  • Options
    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.
Sign In or Register to comment.