May 31, 2010 at 4:47 am
On an instance in our production environment we have a default TAPE destination backup which needs to be changed. There are 250+ databases on the instance. Is there a way to change the destination (with a script?) for all databases? Doing this all by hand is a tedious job to say the least.
EDIT: It seems this is not possible, you will need to change the registry in some way. We have solved it by creating a script doing the backup and run it against the sys.databases tabel.
Greetz,
Hans Brouwer
May 31, 2010 at 9:51 am
It is possible if you use Powershell and SMO - where you have access to that default value. You can build a script to loop through each database and update that property.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 1, 2010 at 1:19 am
How about this:
-- Alter Local Default Backup Directory and check existance / create if needed
--
Declare @NewDrive nchar(1)
Declare @RootDirectory2Add nvarchar(512)
Declare @NewDirectoryPath nvarchar(512)
Declare @Override char(1)
select @NewDrive = N'K'
, @RootDirectory2Add = ''
, @NewDirectoryPath = '' -- default blanc ! -- 'J:\MSSQL.2\MSSQL\Backup' -->@Override = Y needed !!
, @Override = upper('N')
set nocount on
declare @CmdShellSettingBefore table (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))
insert into @CmdShellSettingBefore
EXEC sys.sp_configure N'xp_cmdshell'
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
end
-- Default-path opvragen
declare @DefaultBackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
if @DefaultBackupDirectory like '_:\MSSQL.%'
or @Override = 'Y'
begin
if @NewDrive = N'' set @NewDrive = substring(@DefaultBackupDirectory,1,1)
print N'-- OLD path ' + @DefaultBackupDirectory
if @Override = 'Y'
and @NewDirectoryPath != ''
begin
set @DefaultBackupDirectory = @RootDirectory2Add
end
else
begin
select @DefaultBackupDirectory = @NewDrive + substring(@DefaultBackupDirectory,2,2 )
+ @RootDirectory2Add
+ substring(@DefaultBackupDirectory,charindex(@DefaultBackupDirectory,':') + 3, datalength(@DefaultBackupDirectory))
end
select @DefaultBackupDirectory = replace(@DefaultBackupDirectory, '\\','\')
declare @DOSCmd nvarchar(4000)
select @DOSCmd = N'if not exist "' + @DefaultBackupDirectory + N'" md "' + @DefaultBackupDirectory + N'"'
--print @DOSCmd
exec master..xp_cmdshell @DOSCmd, no_output
print '-- New Default Backup Directory'
print @DefaultBackupDirectory
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', REG_SZ, @DefaultBackupDirectory
end
else
begin
print ' '
print '-- Standard Default Backup Directory has already been modified, use @Override=Y '
print @DefaultBackupDirectory
print '-- Default Backup Directory NOT altered !'
end
-- cmdshell terug afzetten indien ze af stond
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'0'
RECONFIGURE WITH OVERRIDE
end
Keep in mind, you'll still need to specify the final destination in the actual backup statements.
(unless you use sqlserver backup devices, where you need to specify the location with sp_addumpdevice)
Also keep in mind to grant the needed Windows authority for your service account.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 1, 2010 at 11:18 pm
Tnx for answering all.
Tnx for the script, Alz. I'll try it and probly use it. Any idea how to go about this in a Clustered environment? I'm simple, so I'd think I have to run this on both the active as the passive node, but I'm not sure that will really work.
Greetz,
Hans Brouwer
June 2, 2010 at 12:12 am
for your cluster, off course the backup location will be the same (for drives will fail over or UNC drives are the same)
If I'm correct these registry keys are replicated on the cluster that hosts your sql instance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 2, 2010 at 10:11 pm
I use the same method as ALZDBA; however, you should be aware that the procedures being used are not supported and are subject to change or deletion without advance notice.
The Powershell/SMO solution is supported.
June 3, 2010 at 6:02 am
Tnx for answering all, I'll keep the warning in mind.
Greetz,
Hans Brouwer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply