/** NOTE: --- Map drive to allow seamless integration Process for attaching and detaching database from the vTDM Server @param attach_or_detach BIT (Boolean 0 for attaching the database greater than 0 for detaching @param user String Drive UNC Username @param pwd String Drive UNC Password @param DriveLetter Mapped Drive available to map Must NOT EXIST @param DriveName String the UNC drive name e.g \\serverName\driveName\fileName @param DatabaseName String the Database name to create as attached file @param DatabaseMDIF String the .mdif file name in unc path @param DatabaseLDIF String the .ldf file name in unc path @param TDMServer URL the path or server name to access the appliance ---- Derived Values @param BaseFile UNC path until drive @param MFile Complete UNC path for MDif file @param LFile Complete UNC path for LDif file */ -- Configuration Attributes Required DECLARE @attach_or_detach BIT=1 DECLARE @TDMServer Varchar(200)='tmdServer.casa.co.za' DECLARE @user varchar (200)=N'vtdm' DECLARE @pwd varchar (100)=N'vtdm' --- Database Attribute Required DECLARE @DriveName Varchar(150)=N'database_employees_clone1' DECLARE @DriveLetter varchar(5)=N'M:' DECLARE @DatabaseName Varchar(200)=N'database_employees_clone1' DECLARE @DatabaseMDIF Varchar(200)=N'employee.mdf' DECLARE @DatabaseLDIF Varchar(200)=N'employee_log.LDF' -- Derived Attributes DECLARE @BaseFile Varchar(250) DECLARE @MFile Varchar(500) DECLARE @LFile Varchar(500) DECLARE @MapDrive Varchar(500) DECLARE @UnMapDrive Varchar(500) SET @BaseFile=N'\\'+@TDMServer+N'\'+@DriveName SET @MapDrive=N'net use '+@DriveLetter+N' '+@BaseFile+' /USER:'+@user+N' '+@pwd+N' /PERSISTENT:yes' SET @UnMapDrive=N'net use /delete '+@DriveLetter SET @MFile=@DriveLetter+N'\'+@DatabaseMDIF SET @LFile=@DriveLetter+N'\'+@DatabaseLDIF --- Comments print '############Basic Info############' print 'Drive Letter: '+@DriveLetter print 'Drive Path: '+@MapDrive print '###Files Mapped:###' print 'MDIF:'+@MFile print 'LDIF:'+@LFile print '############End Basic Info############' IF(@attach_or_detach=0) BEGIN print '################Executing command################' print N'EXEC xp_cmdshell '+''''+@MapDrive+'''' print N'EXEC sp_attach_db @dbname = '+''''+@DatabaseName+''''+N',@filename1 = '+''''+@MFile+''''+N', @filename2 = '+''''+@LFile+'''' print '############Begin Execution############' EXEC xp_cmdshell @MapDrive print 'Mapping Executed' EXEC sp_attach_db @dbname = @DatabaseName, @filename1 = @MFile, @filename2 = @LFile print 'Attached Executed' print '############End Execution############' END ELSE BEGIN print '################Executing command################' print N'EXEC sp_detach_db @dbname = '+''''+@DatabaseName+'''' print N'EXEC xp_cmdshell '+''''+@UnMapDrive+'''' print '############Begin Execution############' EXEC sp_detach_db @dbname =@DatabaseName print 'Dettached Executed' EXEC xp_cmdshell @UnMapDrive print 'Delete Mapping Executed' print '############End Execution############' END