Powershell run sql scripts
Jul 18
In my work today I have to run many sql scripts that other have written. We are talking 100 of them. They are configurations, T-SQL code changes, moving data, patches from developers. They can be in nested directories. Some times some of them fail for different reason. I have used Powershell for this. But decided now to try to refine the script and also make it easier for others to run the script.
You call the script in its simplest form by
.\run-sql.ps1 |
it will then prompt you for the path where the T-SQL script folder is. It will examine any subfolders and try to run any script in them. It will as default run on the localhost Sqlserver if nothing else is said. Any script that does not work will be renamed from badscript.sql to badscript.failed. All bad script will also be output from the script. So you could pipe the directory to run the script and pipe the output to a copy to folder to examin.
like this:
"t:\testsql\"|.\run-sql.ps1 -ren 0|copy-Item -Destination "t:\failedscript\" |
So I guess the script could be used for running test scripts as well.
The core of the script is:
# Get all sql files to be run. Check also subfolders. Only include sql files Get-ChildItem -path $SqlDir -recurse -Filter *.sql #and #run sqlcmd on where $f.fullname is the path to each file Invoke-SqlCmd -ServerInstance $SQLServer -InputFile $f.fullname |
The rest of the script is for handling loading of the snap in and some error. And lots of comments. So you could write:
Get-Help .\run-sql.ps1 |
to get help.
To be able to use invoke-sqlcmd you have to have two snapp-in installed on your machine where you run the script.
sqlserverprovidersnapin100
sqlservercmdletsnapin100
They should come with a SQL Server 2008 installation or can be downloaded from Microsoft.
I have a few utility powershell script that I use for different task. I have decided to put them on my github repository so maybe others can help me improve them.
I named it powershell påsen. Påsen is swedish for the bag. So i spelled it powershell-pasen. Follow this link for the latest version: https://github.com/patriklindstrom/Powershell-pasen
Here is the first version.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | <# .SYNOPSIS Runs all sql script in a folder. Outputs an array of sqlscripts that failed .DESCRIPTION All files ending with .sql will recursivly be run against localhost sqlserver or the sqlserver given in parameter $SQLServer. If a SQL file generates a sql error its extension it can be changed from .sql to .failed this is so this file will not be run again on a rerun. The script outpus an Array of the files that failed. .EXAMPLE runs .\run-sql.ps1 -SqlDir .EXAMPLE Pipe directory "T:\testSql\"|.\run-sql.ps1 -SqlDir .EXAMPLE Directory as parameter .\run-sql.ps1 -SqlDir "T:\testSql\" .EXAMPLE All parameters .\run-sql.ps1 -SqlDir "T:\testSql\" -SQLServer "BigSqlServer" -RenameFaultyScript 0 -OutputPath "t:\logoutputforScripts" .EXAMPLE exempel with alias run-sql -SqlDir[sql,sd,dir] "T:\testSql\" -SQLServer[server,s] "BigSqlServer" -RenameFaultyScript[ren] 1 -OutputPath [o] "t:\logoutputforScripts" .EXAMPLE Example with do not rename bad scripts copy them into a failed folder instead. Do this by setting the renameparameter to false (0) and pipe outcome to copy-item "t:\testsql\"|.\run-sql.ps1 -ren 0|copy-Item -Destination "t:\failedscript\" .PARAMETER SqlDir The full path to the directory where all the sql script files are. Eg T:\goodstuff\sqltorun . Has alias: dir, sql, ds. Can also be piped into the script. .PARAMETER SQLServer The name of the sql server that the scripts should be run on. If you have an instance name just use "BigSqlServer\InstansName". Has alias server and s. Note that default is localhost. So if you forget to give this parameter but mean to run it on another server you could get suprised or very sad. .PARAMETER RenameFaultyScript Any script that generates a SQL error gets its extension changed from .sql to .failed.Default is that it is true eg 1 or $TRUE. Alias is ren. .PARAMETER OutputPath This is where the output from the sql scripts get saved. If it does not exist it creates an output folder in the root of the SQLDir .PARAMETER OutPut $FaultyFiles is an array of path to where the bad sql scripts are. .LINK latest version http://github.com/patriklindstrom/Powershell-pasen .LINK About Author and script https://www.lcube.se .LINK About powershell for SQL Server http://msdn.microsoft.com/en-us/library/hh245198.aspx .NOTES File Name : run-sql.ps1 Author : Patrik Lindström LCube Requires : PowerShell V2 CTP3 These snapins must have been installed before you can run this powershell. They should come with sqlserver 2008 or should be avaible from Microsoft. sqlserverprovidersnapin100 sqlservercmdletsnapin100 #> param ( [Parameter( Position=0, Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true) ] [Alias('sql')] [Alias('dir')] [Alias('ds')] [string]$SqlDir , [Parameter( Position=1, Mandatory=$false, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$true) ] [Alias('server')] [Alias('s')] [string]$SQLServer="localhost" , [Parameter( Position=2, Mandatory=$false, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$true) ] [Alias('ren')] [boolean]$RenameFaultyScript=$TRUE , [Parameter( Position=3, Mandatory=$false, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$true) ] [Alias('o')] [string]$OutputPath ) # Test for existence of SQL script directory path if (!$SqlDir) { $(Throw 'Missing argument: SqlDir') } if (-not $SqlDir.EndsWith("\")) { $SqlDir += "\" } if (!(test-path $SqlDir)) { $(Throw "The SqlDir: $SqlDir does not exist") } #Test for the OutputPath if (!($OutputPath)) { $OutputPath = join-path -path $SqlDir -childpath "Output" New-Item $OutputPath -type directory -force } if (!(test-path $OutputPath)) { $OutputPath = join-path -path $SqlDir -childpath "Output" Write-Verbose SQL script output directory does not exists. Creates one here $OutputPath New-Item $OutputPath -type directory -force } Add-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyCOntinue -ErrorVariable errSnap1 if ($errSnap1){ if($errSnap1[0].Exception.Message.Contains( 'because it is already added')){ Write-Verbose "sqlserverprovidersnapin100 already added!" $error.clear() }else{ Write-Verbose "an error occurred:$($err[0])." exit } }else{ Write-Verbose "sqlserverprovidersnapin100 Snapin installed" } Add-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyCOntinue -ErrorVariable errSnap2 if ($errSnap2){ if($errSnap2[0].Exception.Message.Contains( 'because it is already added')){ Write-Verbose "sqlservercmdletsnapin100 already added!" $error.clear() }else{ Write-Verbose "an error occurred:$($err[0])." exit } }else{ Write-Verbose "sqlservercmdletsnapin100 Snapin installed" } # $sqlScriptTree = Get-ChildItem -path $SqlDir -recurse -Filter *.sql | sort-object $FaultyFiles = @() $start = Get-Date $i=0 write-host *************** foreach ($f in Get-ChildItem -path $SqlDir -recurse -Filter *.sql | sort-object ) { $out = join-path -path $OutputPath -childpath $([System.IO.Path]::ChangeExtension($f.name, ".txt")) ; $dt = Get-Date -Format s write-host $f.fullname,$dt invoke-sqlcmd -ServerInstance $SQLServer -OutputSqlErrors $TRUE -ErrorAction SilentlyContinue -InputFile $f.fullname | format-table | out-file -filePath $out if ($error){ write-host "SQL error in $($f.fullname) " -foregroundcolor red if ($RenameFaultyScript) { write-host "Changing extension for $($f.fullname) to $([System.IO.Path]::ChangeExtension($f.name, ".failed")) " $FaultyFiles += join-path -path $($f.fullname|split-path) -childpath $([System.IO.Path]::ChangeExtension($f.name, ".failed")) Rename-Item -Path $f.fullname -NewName $([System.IO.Path]::ChangeExtension($f.name, ".failed")) } else { $FaultyFiles +=$f } $error.clear() } ++$i } $dt = Get-Date -Format s $now= Get-Date $ddiff = $now - $start write-host *************** write-host "Done running all $i scripts in $SqlDir on Sqlserver: $SQLServerPath at $dt it took $ddiff" -ForegroundColor green Write-Output $FaultyFiles |