Why aren't all the indexes created?

AxeGrimm · October 18, 2023, 8:00pm

Hi forum. I’m new in PS but i tried to find the answer so hard for my own. I’m trying to create all the indexes to 4k tables, the problem is: i can get a lot of them but there’s some exceptions that i cannot get and i don’t know why. I thought $ScriptIndex.Options.Indexes = $True should be enough to get all indexes. The section of the code:

foreach ($table in $TBLs | where {!($_.IsSystemObject)}  ){
            foreach ($Index in $table.Indexes | where {!($_.IsSystemObject)}){
            $ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
            $ScriptIndex.Options = $CreationScriptOptions
            $ScriptIndex.Options.DriAll = $True
            $ScriptIndex.Options.Indexes = $True
            $Bien = $Index.GetType().Name
            $TypeFolder= "$($Bien)_ParaVerificarPKs"
            Write-Host "$TypeFolder"
            $ScriptIndex.Options.FileName = "$finalPath\$TypeFolder.SQL"        
            $ScriptIndex.Script($Index) 
            }
        }

I compared some tables to see an specific reason about why and i didn’t notice anything, some of the tables with no indexes have a FK as well but other tables have FK too, Other thing that i thought was the cluster or non-cluster, but, same case, some tables with no indexes are non-cluster and cluster. I hope someone can help me, i’m stuck for weeks. And sorry for the grammar.

AxeGrimm · October 20, 2023, 3:31pm

@Olaf Hello, do you know something about the problem…?

Olaf · October 20, 2023, 3:39pm

Why are you asking particularily me? I actually don’t have experiences with SQL-Servers besides installing updates and restarting the servers.

It might be a good idea to ask your question in an SQL related forum as well.

AxeGrimm · October 20, 2023, 4:43pm

Well, first of all, sorry for that, and well, u were answering a lot so maybe you could know something about it, and well, i’m using powershell to do it, but yeh… i guess it’s a good idea ask it in SQL forums, but, thanks!

Olaf · October 20, 2023, 6:21pm

Sorry.

In general …

Did you check that the ones you miss are included in …

and

dotnVo · October 23, 2023, 1:13pm

Unless we are provided with all the data it’s difficult to help and is based on assumptions. How are you populating the variable $tbls? Could you provide the exact code you are using to populate it?

AxeGrimm · November 22, 2023, 11:56pm

Sure, and i’m so sorry for not answering, i’ve been busy with a lot of other stuffs in my job. But, i hope can help.

well i gotta mention the script was created over 10 years ago, and i usually don’t develop for PowerShell or SQL, the function is:

function global:Script-DBObjectsIntoFoldersAIndexes([string]$server, [string]$dbname, [string]$user, [string]$pass, [string]$Path, [string]$bcp_path, [boolean]$Giro, [string]$prefijoEmp, [System.Data.DataTable]$TblO, [System.Data.DataTable]$TblIndx){

$SPs = @()
$TBLs = @()
$USRs = @()
$TBLs2 = @()

$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
#This sets the connection to mixed-mode authentication 
$SMOserver.ConnectionContext.LoginSecure=$false; 
#This sets the login name 
$SMOserver.ConnectionContext.set_Login($user); 
#This sets the password 
$SMOserver.ConnectionContext.set_Password($pass); 
$db = $SMOserver.databases[$dbname.trim()]

$DateFolder = get-date -format yyyyMMdd
$NewFolder = $($prefijoEmp)+$($dbname.trim())+"_"+$($DateFolder.trim())+"_"+$($letra) #ALGA 04/05/16 Se agrego la letra que identifica si es Operativo(O) o Estadístico(E)
$SavePath = $Path + $NewFolder


$SPs += $db.StoredProcedures | where {!($_.IsSystemObject)}
#Cargar en el arreglo solo las tablas operativas o estadísticas ALGA 05/05/16
#RJSV 16/08/2017 -- Se separan las tablas por bases de datos que son de giro porque el objeto $db solo puede obtener tablas de dbo
if($Giro){
    Foreach ($tbs in $tblO.Rows){
        #$TBLs += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
        $table = New-Object Microsoft.SqlServer.Management.Smo.Table($db, $tbs[0], 'Supervisor_giro')
        $table.Initialize()
        $table.Refresh()
        $TBLs += $table
    }
}else{
    Foreach ($tbs in $tblO.Rows){
        $TBLs += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
    }
}

#Carga todas las tablas para crear todos los ínidices en la carpeta de O
if($Giro){
    Foreach ($tbs in $TblIndx.Rows){
        #$TBLs += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
        $table = New-Object Microsoft.SqlServer.Management.Smo.Table($db, $tbs[0], 'Supervisor_giro')
        $table.Initialize()
        $table.Refresh()
        $TBLs2 += $table
    }
}else{
    Foreach ($tbs in $TblIndx.Rows){
        $TBLs2 += $db.Tables[$tbs[0]] | where {!($_.IsSystemObject)}
    }
}

$USRs += $db.Users | where {!($_.IsSystemObject)}

if(Test-Path "$SavePath"){
	Write-Host "Verify directory $SavePath" | Out-File -Filepath "$($SavePath.trim())\$($dbname.trim())_Log.txt" -Append -Force
}else{
	new-item -type directory -name "$NewFolder"-path "$Path"
}

if(Test-Path "$($SavePath.trim())\$($dbname.trim())_Log.txt"){
	$Msg =  "Inicia respaldo"  
	Write-Host (Get-Date).ToString() " $($Msg)"  
	Out-File  -Filepath "$($SavePath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
}else{
	new-item -type file -name "$($dbname.trim())_Log.txt" -path "$($SavePath.trim())"
	$Msg =  "Inicia respaldo"  
	Write-Host (Get-Date).ToString() " $($Msg)"  
	Out-File  -Filepath "$($SavePath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
}


$finalPath = "$($SavePath.trim())"
#Declaramos las opciones generales para hacer el script
	write-host "configure script options"
	$CreationScriptOptions = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")
	$CreationScriptOptions.AppendToFile = $True
	$CreationScriptOptions.AllowSystemObjects = $false
	$CreationScriptOptions.ToFileOnly = $true
	$CreationScriptOptions.IncludeIfNotExists = $true
	$CreationScriptOptions.IncludeDatabaseRoleMemberships = $True
	$CreationScriptOptions.IncludeDatabaseContext = $True

#En esta parte se ah decidido crear por separado los SP, TRIGGERS, INDICES, TABLAS y BD.
	<# Se crea el script para las tablas , usuarios, roles etc. SOLO PARA RESPALDOS OPERATIVOS#>

If ($contador -eq 0){ #INICIO DEL IF 1 --Solo se hará para los respaldos operativos
$Tcount = 0

Do{
	try{
        #$query= 'USE AdmonRespaldos select DB_TablaNombre from DB_tablas where DB_TablaOrigen=''' + $BDNOMBRE + ''' order by DB_TablaId'
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		$scriptTransfer = new-object ('Microsoft.SqlServer.Management.Smo.Transfer') $db
       
		$scriptTransfer.options=$CreationScriptOptions
		$scriptTransfer.options.Permissions = $true 
		$scriptTransfer.options.FileName = "$finalPath\DBobjectsScript.SQL"
		$scriptTransfer.CopyAllDefaults=$False
		$scriptTransfer.CopyAllStoredProcedures=$False
		$scriptTransfer.CopyAllDatabaseTriggers =$False
		$scriptTransfer.CopyAllObjects =$False 
        $scriptTransfer.CopyAllTables=$True
		$scriptTransfer.CopyAllViews=$true
		$scriptTransfer.CopyAllRoles=$true
		$scriptTransfer.CopyAllRules=$true
		$scriptTransfer.CopyAllSchemas=$true        
		#RJSV 16/08/2017 -- Se separan del respaldo de DBobjects principal
        #$scriptTransfer.CopyAllUsers=$true
		#$scriptTransfer.CopyAllLogins=$true
        $scriptTransfer.CopyAllUserDefinedFunctions=$true #ALGA 05/05/16 Se agregó para copiar las funciones de la BD
		#RJSV 16/08/2017 -- Se agregan opciones para copiar tipos de datos y tipos de tablas definidas por usuarios
        $scriptTransfer.CopyAllUserDefinedDataTypes=$true
        $scriptTransfer.CopyAllUserDefinedTableTypes=$true
        
        #RJSV 25/08/2017 -- Solucion tmp indices de giro no se pueden generar en Index.SQL ni los triggers
        if($Giro){
           $scriptTransfer.options.Indexes = $True
           $scriptTransfer.options.DriAll = $True
           $scriptTransfer.Options.Triggers= $True 
        }
        
        $scriptTransfer.DestinationDatabase = ' '
		$scriptTransfer.ScriptTransfer()
		
		if(Test-Path "$finalPath\DBobjectsScript.SQL"){
			$Msg = "Done create DB.Objects script" 
			write-host "Done create DB.Objects script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "error while creating script creat DB.  $_ .Trying again..." 
			write-host "error while creating script creat DB.  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
		
	}Catch [system.exception]{
		$Msg = "error while creating script creat DB.  $_" 
		Write-Error "error while creating script creat DB.  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\DBobjectsScript.SQL"))

#RJSV 28/08/2017 - Se separan los permisos de los usuarios por base de datos
$Tcount = 0
Do{
	try{
		$Msg = "Scripting Permissions..." 
		write-host "Scripting Permissions..." 
        
        if($Giro){
        Exportar-Permisos -SqlInstance $server -uDecrypt $user -pDecrypt $pass -FilePath "$finalPath\DBpermissionsScript.SQL" -Database $dbname -Giro $True
        }else{
        Exportar-Permisos -SqlInstance $server -uDecrypt $user -pDecrypt $pass -FilePath "$finalPath\DBpermissionsScript.SQL" -Database $dbname
        }
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
	
		
		if(Test-Path "$finalPath\DBpermissionsScript.SQL"){
			$Msg = "Done create DBpermissionsScript script" 
			write-host "Done create DBpermissionsScript script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "error while creating script creat DB.  $_ .Trying again..." 
			write-host "error while creating script creat DB.  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
		
	}Catch [system.exception]{
		$Msg = "error while creating script creat DB.  $_" 
		Write-Error "error while creating script creat DB.  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\DBpermissionsScript.SQL"))


	<# Se crea el script DROP SOLO EN RESPALDOS OPERATIVOS#>

$Tcount = 0
Do {
	try{
		$scriptrDBdrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

		$scriptrDBdrop.Options = $CreationScriptOptions
		$scriptrDBdrop.Options.Permissions = $true 
		$scriptrDBdrop.Options.ScriptDrops = $True
		$scriptrDBdrop.Options.FileName = "$finalPath\$($dbname.trim())_drop.SQL"
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		<#Crea script para eliminar la base de datos anterior#>
		$scriptrDBdrop.Script($db)
		if(Test-Path "$finalPath\$($dbname.trim())_drop.SQL"){
			$Msg = "Done drop DB script" 
			write-host "Done drop DB script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "error while creating script drop DB $_ . Trying again..." 
			write-host "error while creating script drop DB $_ . Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
		
	}Catch [system.exception]{
		$Msg =   "error while creating script drop DB $_" 
		Write-Error "error while creating script drop DB $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\$($dbname.trim())_drop.SQL"))
	
	<# Se crea el script Crear base de datos (no se utiliza realmente) SOLO RESPALDOS OPERATIVOS #>
$Tcount = 0
Do{
	try{
		$scriptrDB = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

		$scriptrDB.Options = $CreationScriptOptions
		$scriptrDB.Options.Permissions = $true 
		$scriptrDB.Options.ScriptDrops = $False
		$scriptrDB.Options.DdlBodyOnly = $False
		$scriptrDB.Options.FileName = "$finalPath\$($dbname.trim())_perm.SQL"

		$Msg =  "Scripting..."
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg

		$scriptrDB.Script($db)
		
		if(Test-Path "$finalPath\$($dbname.trim())_perm.SQL"){
			$Msg =  "Done create DB script" 
			write-host "Done create DB script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg =  "error while creating script creat DB. $_ . Trying again..." 
			write-host "error while creating script creat DB. $_ . Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		$Msg = "error while creating script creat DB. $_" 
		Write-Error "error while creating script creat DB. $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}While($contador -eq 1)
#While(-Not (Test-Path "$finalPath\$($dbname.trim())_perm.SQL"))

	<# Se crea el script para los SP SOLO RESPALDOS OPERATIVOS#>
$Tcount = 0	
Do{
	try{
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		$scriptrDBSP = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)

		$scriptrDBSP.Options = $CreationScriptOptions
		$scriptrDBSP.Options.Permissions = $True
		$scriptrDBSP.options.FileName = "$finalPath\DB_SP.SQL"
		
		if(($SPs.Length -ne 0) -and ($SPs -ne '')){
			foreach($SP in $SPs | where {!($_.IsSystemObject)}){
				$scriptrDBSP.Script($SP) | Out-null
			}
		}
		if(Test-Path "$finalPath\DB_SP.SQL"){
			$Msg =  "Done create SP's script" 
			write-host "Done create SP's script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}else{
			$Msg =  "There are no Stored Procedures to generate the script." 
			Write-Host "There are no Stored Procedures to generate the script." -Fore Green
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		$Msg = "error while creating script SP. $_" 
		Write-Error "error while creating script SP. $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\DB_SP.SQL"))
}#FIN del IF 1

	<# Se generan los .txt del BCP con la informacion de las tablas SE GENERAN POR TABLA #>
try{
	$Msg = (Get-Date).ToString()+ " Starting bcp backup"
	write-host "Starting bcp backup" 
	Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg

   ##AQUI SE GENERAN LOS TXTS DE LAS TABLAS## -- ALGA
	Foreach ($tb in $tblO.Rows){
		if($Giro  -eq $true){
	            	$arguments = "$($db.Name).Supervisor_Giro.$($tb[0])",
	                        'out',"$($finalPath)\$($tb[0]).txt",
	                        '-t"²"',
	                        '-r"{CR}{LF}"',
			        '-o',"$($finalPath)\BCP_log.txt",
	                        '-q',
	                        '-c',
		     		'-k',
	                        '-U',"$($user)",
	                        '-P',"$($pass)",
	                        '-S',"$($server)"
		}else{
	            	$arguments = "$($db.Name).dbo.$($tb[0])",
	                        'out',"$($finalPath)\$($tb[0]).txt",
	                        '-t"²"',
	                        '-r"{CR}{LF}"',
			        '-o',"$($finalPath)\BCP_log.txt",
	                        '-q',
	                        '-c',
		     		'-k',
	                        '-U',"$($user)",
	                        '-P',"$($pass)",
	                        '-S',"$($server)"
		}

		& $bcp_path $arguments | Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force

		$Msg = "Finish $($tb[0])" 
		write-host "Finish $($tb[0])" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
    }

	$Msg = (Get-Date).ToString() +"Finish bcp backup"
	write-host "Finish bcp backup" 
	Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
}Catch [system.exception]{
	$Msg = "Error during the bcp backup process $_" 
	Write-Error "Error during the bcp backup process $_" 
	Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
    break;
}

#>


	<# Se crea el script para los indices SE GENERAN POR TABLA#>
If(-Not ($Giro)){
$Tcount = 0
Do{
	try{      
		$Msg = "Scripting Indexes..." 
		write-host "Scripting Indexes..." 
        write-host "$($finalPath.trim())\$($dbname.trim())_Log.txt"
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        
        
		foreach ($table in $TBLs2 | where {!($_.IsSystemObject)}  ){
			foreach ($Index in $table.Indexes | where {!($_.IsSystemObject)}){
			$ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
			$ScriptIndex.Options = $CreationScriptOptions
			$ScriptIndex.Options.DriAll = $True
			$ScriptIndex.Options.Indexes = $True
			$TypeFolder=$Index.GetType().Name
			$ScriptIndex.Options.FileName = "$finalPath\$TypeFolder.SQL"		
			$ScriptIndex.Script($Index) 
			}
		}
        
		if(Test-Path "$finalPath\$TypeFolder.SQL"){
			$Msg = "Done create indexes script" 
			write-host "Done create indexes script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			
            $Msg = "error while creating script creat DB  $_ .Trying again..." 
			write-host "error while creating script creat DB  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		
        $Msg = "error while creating script creat DB  $_" 
		Write-Error "error while creating script creat DB  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While(-Not (Test-Path "$finalPath\$TypeFolder.SQL"))


$Tcount = 0

    <#Indexes solo Operativos en IndexParaVerificar.sql    #>
    <#
Do{
	try{

        
		$Msg = "Scripting Indexes..." 
		write-host "Scripting Indexes..." 
        write-host "$($finalPath.trim())\$($dbname.trim())_Log.txt"
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        
        
		foreach ($table in $TBLs | where {!($_.IsSystemObject)}  ){
			foreach ($Index in $table.Indexes | where {!($_.IsSystemObject)}){
			$ScriptIndex = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
			$ScriptIndex.Options = $CreationScriptOptions
			$ScriptIndex.Options.DriAll = $True
			$ScriptIndex.Options.Indexes = $True
            $Bien = $Index.GetType().Name
			$TypeFolder= "$($Bien)_ParaVerificarPKs"
            Write-Host "$TypeFolder"
			$ScriptIndex.Options.FileName = "$finalPath\$TypeFolder.SQL"		
			$ScriptIndex.Script($Index) 
			}
		}
        
		if(Test-Path "$finalPath\$TypeFolder.SQL"){
			$Msg = "Done create indexes script" 
			write-host "Done create indexes script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			
            $Msg = "error while creating script creat DB  $_ .Trying again..." 
			write-host "error while creating script creat DB  $_ .Trying again..." 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
	}Catch [system.exception]{
		
        $Msg = "error while creating script creat DB  $_" 
		Write-Error "error while creating script creat DB  $_" 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
        break;
	}
}
While($contador -eq 1)
#>


	<# Se crea el script para los triggers SE GENERAN POR TABLA #>

$Tcount = 0
Do{
		$Msg = "Scripting..." 
		write-host "Scripting..." 
		Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		$scriptTable = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
		$scriptTable.Options = $CreationScriptOptions
		$scriptTable.options.Permissions = $True 
		$scriptTable.Options.Triggers= $True 
		$scriptTable.Options.IncludeDatabaseContext = $True
		$scriptTable.Options.FileName = "$finalPath\DB_Triggers.SQL"
       
		If (($TBLs2.Length -ne 0) -and ($TBLs2 -ne '')){
			foreach($TBL in $TBLs2 | where {!($_.IsSystemObject)}){#Aquí truena con DWH
				foreach($Triggers in $TBL.Triggers){
					$scriptTable.Script($Triggers)
				}
			}
		}
		
		if(Test-Path "$finalPath\DB_Triggers.SQL"){
			$Msg = "Done create DB Triggers script" 
			write-host "Done create DB Triggers script" 
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
		}Else{
			$Msg = "There are no Triggers to generate the script." 
			write-host "There are no Triggers to generate the script."  
			Out-File -Filepath "$($finalPath.trim())\$($dbname.trim())_Log.txt" -Append -Force -InputObject $Msg
			if($Tcount -eq 2){break}
			$Tcount = $Tcount + 1
		}
}
While(-Not (Test-Path "$finalPath\DB_Triggers.SQL"))
}

} #This completes the function

So, I think the loop works because it brings the backup for all the tables with no exception, and well, the functions is called in:

$queryFull= 'USE AdmonRespaldos select DB_TablaNombre from DB_tablas where DB_TablaOrigen=''' + $BDNOMBRE + ''' order by DB_TablaId'

#Llama a las tablas por su tipo
$queryEsp= 'USE AdmonRespaldos select DB_TablaNombre from DB_tablas where ' + $versionRespaldo + ' and DB_TablaOrigen=''' + $BDNOMBRE + ''' order by DB_TablaId'



$cmd2=new-object system.Data.SqlClient.SqlCommand($queryEsp,$cnn)
$cmd2.CommandTimeout=120
$cnn.Open()
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $cmd2
$tables = New-Object System.Data.DataSet
$SqlAdapter.Fill($tables)
$res=New-Object System.Data.DataTable
$res=$tables.Tables[0]

$cmd3=new-object system.Data.SqlClient.SqlCommand($queryFull,$cnn)
$cmd3.CommandTimeout=120
$SqlAdapter3 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter3.SelectCommand = $cmd3
$tables3 = New-Object System.Data.DataSet
$SqlAdapter3.Fill($tables3)
$res3=New-Object System.Data.DataTable
$res3=$tables3.Tables[0]

$cnn.Close()


    #Aquí llama la función que crea todos los objetos, enviando los parámetros de la BD actual-- ALGA 
    If ($contador -eq 0){
        Script-DBObjectsIntoFoldersAIndexes $server $BDNOMBRE $udecrypted $pdecrypted $path $bcp_path $Giro $prefijoEmp $res $res3
	}Else{
        Script-DBObjectsIntoFolders $server $BDNOMBRE $udecrypted $pdecrypted $path $bcp_path $Giro $prefijoEmp $res
    }

And thanks again for all! Maybe with this could be more understandable. Again, sorry for be inactive

Topic		Replies	Views
SMO scripting doesn't generate Create Statistics PowerShell Help	1	170	October 6, 2016
Backup specific MS SQL databases PowerShell Help	0	134	December 31, 2011
Writing to SQL tables PowerShell Help	0	185	December 31, 2011
Powershell 3.0 and SQL 2012 not scripting correctly PowerShell Help	0	159	December 31, 2011
cannot enumerate the collection PowerShell Help	2	284	April 30, 2014

Why aren't all the indexes created?

Related topics