More related to Stock Exchange ...will update soon..

DTS Password Change

Name: Neelesh Kulshrestha
Profile: Sr. SQL DBA
Experience: 7.6 Years
Domain: IT Industry
Client handled: Manufacturing, Financial,stock exchange, Payroll Processing
Email: Neelesh.Kulshrestha@gmail.com
-----------------------------------------------------------------------------------
The tradition code which normally build logshipping through customed stored procedures which are give below is using by many companies including US based too. But this code has some limitation regarding multiple stripe backup and restoring through stripe backup, which is 4 and this hardcoded too, so you cannot choose number of stripes as per your choice.
To remove this limitation i have added the module in logshipping_backup which can take multiple stripes backup more than four dynamically as was hardcoded previously.
Same is for restoration which restore the database from multiple stripes dynamically.
I have provided the code below with all other scripts which might be useful to you.


1. usp_logshipping_init [ modify the code by me to restore the database from multiple files dynamically ]
2. usp_logshipping_continue
3. LS_DBBackup_sp
4. usp_copyfile
5. Log shipping monitor

-----------------------------------------------------------------------------------





Script of Restoration of database from multiple files dynamically



--drop table #filelist --select * from #filelist declare @dbname varchar(50) ,@backuppath varchar(250) = 'c:\backup\' ,@standbyfile varchar(450) = 'c:\abc1_standby.rdo' ,@fileprefix varchar(50) = 'r1_' ,@allbackuppostfix varchar(50) = '.bak*' ,@fullbackuppostfix varchar(50) = '_full.bak' ,@diffbackuppostfix varchar(50) = '_dif.bak' ,@logbackuppostfix varchar(50) = '_trn.bak' --,@fullbackupmovecommand varchar(550) = ' MOVE ''PAD_Data'' TO ''c:\test_data01.mdf'', MOVE ''PAD_Log'' TO ''c:\test_log01.ndf'' ' ,@zippath varchar(100) = 'c:\scripts\' declare @type varchar(25) declare @lastrestorefilename varchar(455) declare @sqlstring varchar(4000) declare @restore_filename varchar(425) declare @backups cursor declare @seq int declare @found_full int declare @error int --if RIGHT(@zippath, 1) != '\' --SET @zippath = @zippath + '\' --if RIGHT(@backuppath, 1) != '\' declare rpt cursor for select name from sysdatabases where name in ('r1', 'abc1') open rpt fetch next from rpt into @dbname while @@FETCH_STATUS = 0 begin SET @backuppath = @backuppath + '\' +@dbname +'\' create table #filelist (seq int identity(1,1), backupfilename varchar(255)) set @sqlstring = 'dir /o /b ' + @backuppath + @dbname + '*' + @allbackuppostfix insert into #filelist (backupfilename) exec master..xp_cmdshell @sqlstring delete from #filelist where backupfilename is null set @backups = CURSOR SCROLL DYNAMIC FOR select max(seq), backupfilename from #filelist where charindex(@fullbackuppostfix, backupfilename) > 0 and seq = (select MAX(seq) from #filelist) group by backupfilename open @backups fetch next from @backups into @seq, @restore_filename if @@fetch_status = 0 begin /* -- check file isnt zipped, if so decompress & restore if charindex('.gz', @restore_filename) > 0 or charindex('.zip', @restore_filename) > 0 begin SELECT @sqlstring = @zippath + 'gzip.exe -d -f ' + @backuppath + @restore_filename EXEC @error = master..xp_cmdshell @sqlstring, NO_OUTPUT set @restore_filename = replace(@restore_filename, '.gz', '') set @restore_filename = replace(@restore_filename, '.zip', '') end -- set @sqlstring = 'exec dbo.usp_KillUsers ''' + @dbname + '''' exec (@sqlstring) */ set @sqlstring = 'RESTORE DATABASE [' + @dbname + '] ' + 'FROM DISK= ' + '''' +@backuppath + @restore_filename+ '''' exec(@sqlstring) fetch next from @backups into @seq, @restore_filename end close @backups deallocate @backups fetch next from rpt into @dbname drop table #filelist end close rpt deallocate rpt


Now ,in day to day activities we face challenges to deal with DTS packages like migration , their password changes etc. These things can be easily handle when number of DTS packages are small. But what will happen if number of DTS packages are round about 600 around and their naming conventions are in hexadecimal format when created through application. I have been in IT industry for 5.11 years and developed many DTS packages to deal with report fetching and emailing to users.Currently i am working with financial domain company, i have suggested solution for restoring database from parallel stripe backup dynamically and logically.
Recently i faced situation where i need to change the password of login which is used in 600 DTS packages because of SOX compliance.
Here is the logic through visual basic script which i have applied to change the password of DTS packages.
This is the script i have posted for which other SQL guys are looking for.

function Main()

Dim sServername
sServername = "Servername" '<<<<<<<<<<<<< SET YOUR SERVERNAME HERE!<<<<<<<<<<<<<< Dim bIntegratedSecuritya bIntegratedSecurity = true '***NOTE: If you use standard security, set this to false and specify a username and password below Dim sLogin Dim sPassword sLogin = "" sPassword = "" Dim oApplication ' As DTS.Application Dim oPackageSQLServer ' As DTS.PackageSQLServer Dim oPackageInfos ' As DTS.PackageInfos Dim oPackageInfo ' As DTS.PackageInfo Dim oPackage ' As DTS.Package Dim oConnection 'As DTS.Connection2 Dim oProp ' As DTS.DtsProperty Set oApplication = CreateObject("DTS.Application") If bIntegratedSecurity Then Set oPackageSQLServer = oApplication.GetPackageSQLServer(sServername, "", "", DTSSQLStgFlag_UseTrustedConnection) Else Set oPackageSQLServer = oApplication.GetPackageSQLServer(sServername, sLogin, sPassword, 0) End If Set oPackageInfos = oPackageSQLServer.EnumPackageInfos("", True, "") Set oPackageInfo = oPackageInfos.Next 'Note: It is IMPORTANT that oPackage be instantiated and destroyed within the loop. Otherwise, 'previous package info will be carried over and snowballed into a bigger package every Time 'this loop is run. That is NOT what you want. Do Until oPackageInfos.EOF Set oPackage = CreateObject("DTS.Package2") '**** INTEGRATED SECURITY METHOD If bIntegratedSecurity Then oPackage.LoadFromSQLServer sServername, , , DTSSQLStgFlag_UseTrustedConnection, , , , oPackageInfo.Name Else '**** STANDARD SECURITY METHOD oPackage.LoadFromSQLServer sServername, sLogin, sPassword, DTSSQLStgFlag_Default, , , , oPackageInfo.Name End If '**** If you want to actually do something to each package (like turn on logging for example) and save them, you could do this here For Each oConnection In oPackage.Connections 'Debug.Print oPackage.Name, oConnection.ConnectionProperties("Data Source ")" If UCase(oConnection.ConnectionProperties("Data Source")) = "Servername" then oConnection.Password = "password" 'Debug.Print oConnection.ConnectionProperties("Data Source") End If Next If bIntegratedSecurity Then oPackage.SaveToSQLServer sServername, , , DTSSQLStgFlag_UseTrustedConnection, , , , oPackageInfo.Name Else '**** STANDARD SECURITY METHOD oPackage.SaveToSQLServer sServername, sLogin, sPassword, DTSSQLStgFlag_Default, , , , oPackageInfo.Name End If 'oPackage.LogToSQLServer = True 'oPackage.LogServerName = sServername 'oPackage.LogServerUserName = sLogin 'oPackage.LogServerPassword = sPassword 'oPackage.LogServerFlags = 0 'oPackage.SaveToSQLServer sServername, sLogin, sPassword, DTSSQLStgFlag_Default Set oConnection = Nothing Set oPackage = Nothing Set oPackageInfo = oPackageInfos.Next Loop 'Clean up and free resources Set oApplication = Nothing Set oPackageSQLServer = Nothing Set oPackageInfos = Nothing Set oPackageInfo = Nothing Set oPackage = Nothing Set FileSys = Nothing Main = DTSTaskExecResult_Success End function ----------------------------------------------------------------------------------- Restoration


Attaching \Detaching multiple databases


--Detaching
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'',"DBAdmin","AuditDB")
BEGIN
SELECT ''EXEC sp_detach_db ''''?'''',''''true''''''
END'







--drop table t1
--drop table t3

/*creating table t1 */

select DB_NAME(dbid) name, fileid, filename into t1 from sys.sysaltfiles


/*creating table t3 where we can change the location of ldf file (in a similar way we can change the location of mdf file)*/

select name, fileid ,REPLACE(filename,'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\' ,'c:\log\') 'filename'
into t3 from t1
where fileid = 2

/* updating table t1 with changed location of ldf file from table t3*/

begin tran
update t1
set filename = t3.filename
from t3
where t1.name = t3.name
and t1.fileid =2
commit tran



delete t1
where name is null or name in ('master','msdb','model','tempdb')



set nocount on

declare @dbname varchar(50)
declare rpt cursor for
select name from t1 group by name

open rpt

fetch next from rpt into @dbname

while @@FETCH_STATUS =0
begin

SELECT 'EXEC sp_attach_db '+ ''''+ @dbname +''''+','+ ''''+ RTRIM(filename) +''''+','
FROM t1
WHERE fileid = (SELECT MIN(fileid) FROM t1 where name = @dbname )
and name = @dbname

UNION ALL
SELECT ''''+RTRIM(filename)+''''+','
FROM t1
WHERE fileid > (SELECT MIN(fileid) FROM t1 where name = @dbname) AND
fileid < (SELECT MAX(fileid) FROM t1 where name = @dbname)
and name = @dbname
UNION ALL
SELECT ''''+RTRIM(filename)+''''
FROM t1
WHERE fileid = (SELECT MAX(fileid) FROM t1 where name = @dbname)
and name = @dbname

fetch next from rpt into @dbname
end
close rpt
deallocate rpt