November 14, 2008 at 9:17 am
hello folks,
i just got this question in mind...is there anyway from management studio we can find the drive location where the backups are stored..like any stored proc or script...i normally remotely login to the server and check for .bak files..dats how i locate...any suugestions..thx
November 14, 2008 at 10:00 am
I assume your backups are running from a scheduled job. If so, open the job, open the step, and the backup script should be there with all the specifics.
November 14, 2008 at 10:51 am
It's a registry key !
have a look a this script ...
-- Alter Local Default Backup Directory
-- xp_fixeddrives
Declare @NewDrive nchar(1)
Declare @RootDirectory2Add nvarchar(512)
Declare @NewDirectoryPath nvarchar(512)
Declare @Override char(1)
select @NewDrive = N'F'
, @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
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
November 14, 2008 at 11:18 am
thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply