How do you configure where your database backups are created? Do you have problems updating all of your backup jobs if the backup location needs to change?
In this blog we will look at how you can make use of Windows settings and a few lines of simple TSQL to have total control over where you database backups are created. We will create backup jobs (or update existing ones) that automatically backup to your chosen directory and when you want to change that location you can make the change in one place and all your servers will run their next backups to the new location.
You will need a few things to follow along at home but these steps should be easily adapted if you are not quite set up as I am.
My chosen backup solution for this blog is Maintenance Solution from Ola Hallengren – http://ola.hallengren.com/. If you dont use this, or have never looked at it you really should. Its a script that builds jobs that take care of your Backups, Integrity checks and Index maintenance.
For the demos here I have 2 directories in C:\Backup_Test. I have one called Demo_01 and another called Demo_02.
Once you have done some reading on Ola’s site run the MaintenanceSolution.sql on your test server. It will create some SQL Agent jobs for you:
In this blog we are only going to be concerning ourselves with the DatabaseBackup jobs, feel free to delete the other jobs from your server whenever you like. I have included the TSQL to drop all of the jobs created so, as long as you keep the names the same, you can clean up after this test.
Now by default these jobs are set to create the database backup files in a directory structure of ServerName\DatabaseName\BackupType\Backupfilename.bak and these are all placed inside the C:\Backups directory. You can check this by editing one of the jobs and looking at the code in step 1
This is set by a line of code in MaintenanceSolution.sql and you could make a change there to make the backups go where you want but then you’d have to drop all jobs and recreate them if you want to make a change to the backup location. On all your servers. Not so much fun. Lets see if we can do it a bit more efficiently.
The details of all the parameters that Ola has built in to his backup solution are described at http://ola.hallengren.com/sql-server-backup.html and the details for @Directory show that if NULL is passed to the procedure then the effect will be that all backups will go to the SQL Server default backup location.
You can check what your SQL Server dafault backup location is in a number of ways but possibly the easiest is to right click your server and choose properties and then the Database Settings tab. In the details you will see the default locations for Data files, Log files and Backup files. Your default values will look like this depending on your version of SQL Server installed.
Now consider that perhaps the network admin team decide the backup device needs to be replaced or you have to switch to an alternative during a maintenance window. Again, you could go to all of your servers and change this value to your required backup location but it still means there is a chore to be done when you need to change this location.
Let’s get some help from T-SQL…
First of all the warnings and disclaimers: This demonstration uses undocumented stored procedures to modify the registry on the computer you are connected to. This is not running with scissors this is sprinting as fast as you can through a scissor factory with all the staff throwing scissors in your direction. Only do this on a test server to see how it works. I am no good with bandages and you may well need them after your manager has finished with you if you break an important server. I am not advising that you do this in production. I cannot and will not help you recover from any problems this code causes. Clear? Good.
So, to the undocumented procedures – xp_regread, xp_instance_regread and xp_instance_regwrite. Information on these procedures is fairly wide-spread but I suppose it can only be described as anecdotal as I can find nothing in an official Microsoft publication like MSDN or Books Online.In short, xp_regread reads values from the registry, xp_instance_regread reads values from the SQL Server instance and xp_instance_regwrite writes values to the SQL Server instance.
If we use xp_instance_regread as below we can programmatically see what the default backup location is:
1 2 3 4 5 6 7 |
/*** read the current SQL Server default backup location ***/ DECLARE @BackupDirectory NVARCHAR(100) EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\MSSQLServer\MSSQLServer', @value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ; SELECT @BackupDirectory AS [SQL Server default backup Value] |
It isn’t a massive leap of faith to see that with xp_instance_regwrite we could put the value we want to use in there …
1 2 3 4 5 6 7 8 9 |
/*** write a value to the SQL Server default backup location ***/ DECLARE @sql AS NVARCHAR(200) SET @sql = 'EXEC master..xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''Software\Microsoft\MSSQLServer\MSSQLServer'', N''BackupDirectory'', REG_SZ, N''C:\BackupTest\Demo_01'''; -- PRINT @sql -- Debug print EXEC [sys].[sp_executesql] @sql; GO |
xp_regread is a procedure like xp_instance_regread in so far as it read the local computer registry but with this we can get to any value in the registry. Like an environment variable such as BackupServer …
1 2 3 4 5 6 7 |
/*** read location from BackupServer env var in registry ***/ DECLARE @BackupServer NVARCHAR(100) EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SYSTEM\ControlSet001\Control\Session Manager\Environment', @value_name = 'BackupServer', @BackupServer = @BackupServer OUTPUT ; SELECT @BackupServer AS [Env Var Value] |
This value can be managed by the network administrator and set as and when they choose, via Group Policy or PowerShell or however they prefer. We, as SQL Administrators, can then simply read the value and update the SQL Server default location before running a backup and know that the backup will be in the default location.
All we need to do is alter the backup job to remove the @Directory = N’C:\Backup’ value and replace it with @Directory = NULL.
This isn’t complex but does take a little bit of string wrangling.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
/*** Code to alter msdb backup jobs to set the Backup Directory to NULL so the job uses the default backup SQL Server locatione ***/ USE [msdb] go BEGIN TRAN; WITH datas1 -- Locate the start of the Backup directory parameter AS ( SELECT s.[job_id] , s2.[step_id] , [command] , PATINDEX('% @Directory = N%,%', [command]) AS [stringstart] FROM [dbo].[sysjobs] AS S INNER JOIN [dbo].[sysjobsteps] AS S2 ON [S2].[job_id] = [S].[job_id] WHERE s.[name] LIKE '%BACKUP%' AND s2.[command] LIKE '% @Directory = N%,%' ), datas2 -- split out the command preceding the backup directory parameter AS ( SELECT [job_id] , [step_id] , [command] , LEFT([command], [stringstart]) AS LeftCommand FROM [datas1] ), datas3 -- strip the LeftCommand out of the whole command and then locate the end of the backup directory parameter AS ( SELECT [job_id] , [step_id] , [command] , [LeftCommand] , REPLACE([command], leftcommand, '') AS RightCommand1 , PATINDEX('%,%', REPLACE([command], leftcommand, '')) AS [string finish] FROM [datas2] ) -- use the derived values to rebuild the Command value with a NULL for the backup dirctory parameter UPDATE [dbo].[sysjobsteps] SET [command] = [LeftCommand] + ' @Directory = NULL' + SUBSTRING([RightCommand1], [string finish], 1000) FROM datas3 INNER JOIN [dbo].[sysjobsteps] AS S ON [S].[job_id] = [datas3].[job_id] AND [S].[step_id] = [datas3].[step_id]; -- sit back with a beverage of your choice and check the results SELECT s.[job_id] , s2.[step_id] , [command] FROM [dbo].[sysjobs] AS S INNER JOIN [dbo].[sysjobsteps] AS S2 ON [S2].[job_id] = [S].[job_id] WHERE s.[name] LIKE '%BACKUP%' AND s2.[command] LIKE '% @Directory = N%,%'; COMMIT |
And that completes the job we set out to achieve – we can deploy the native MaintenanceSolution to any SQL Server in our environment, run the TSQL to set the @Directory parameter to NULL and run a daily script to check the Environment Variable and refresh the SQL Server Backup default value. The backup jobs can run as and when they are scheduled without us having to worry over the target location. The network admin team can control where our backups go and manage their resources as they need to without worrying about what problems it will cause us. We can deploy new versions of MaintenanceSolution as we choose with the knowledge that we simply need to update the backup jobs to replace the @Directory value.
Load comments