Making global changes to all servers

Adam.Bean · October 29, 2007 5:19PM

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!

petey · October 29, 2007 7:53PM

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

Adam.Bean · October 29, 2007 8:07PM

Thanks, I'll start looking into that.

Is this a planned change in an upcoming version?

Adam.Bean · October 30, 2007 9:41AM

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

petey · October 30, 2007 10:30AM

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.

Adam.Bean · October 30, 2007 10:32AM

Perfect, thanks for the explanation!

Adam.Bean · October 30, 2007 10:43AM

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:50AM

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

petey · October 30, 2007 11:05AM

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.

Adam.Bean · October 30, 2007 11:05AM

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.

petey · October 30, 2007 11:16AM

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.

Adam.Bean · October 30, 2007 11:20AM

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:33AM

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.

petey · October 30, 2007 11:36AM

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

Adam.Bean · October 30, 2007 11:38AM

Ah ok, thanks yet again.

nephila · November 2, 2007 4:16PM

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.

Making global changes to all servers

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk