_HowToDeplooy_SQLscripts_UsingPowershell
Uploaded by
arunkumarco_HowToDeplooy_SQLscripts_UsingPowershell
Uploaded by
arunkumarcopowershell command line
$dblist = Invoke-Sqlcmd -ServerInstance ssbcidw04 -Database master -InputFile "I:\
Backups\[Link]" -QueryTimeout 600
above command executes the script in the input file and stores the list of
database names in the dblist parameter
foreach ($db in $dblist) { $[Link] }
above command prints each database name to the screen
foreach ($db in $dblist) { Invoke-Sqlcmd -ServerInstance ssbcidw04 -Database
$[Link] -InputFile "I:\Backups\cust_CustomerLookupSearch.sql" -
QueryTimeout 300 }
above command executes script in the inputfile for each database
-- SSBDWDEV01 below line how I deployed the database DDL trigger saved the trigger
scrip to C:\Temp\Database_DDL_Trigger.sql
cls
$sqlservername = 'ssbcidw05'
$dblist = Invoke-Sqlcmd -ServerInstance $sqlservername -Database master -query
"select name from [Link] where database_id <> 5 and database_id > 4 and
is_read_only = 0 order by name" -QueryTimeout 600
foreach ($db in $dblist) { Invoke-Sqlcmd -ServerInstance $sqlservername -Database
$[Link] -InputFile "C:\Temp\Database_DDL_Trigger.sql" -QueryTimeout 300 }
-- This is how Database_DDL_Audit database trigger deployed to VM-DB-PROD-01 and
VM-DB-PROD-02
cls
$sqlservername = 'VM-DB-PROD-01'
$dblist = Invoke-Sqlcmd -ServerInstance $sqlservername -Database master -InputFile
"C:\Temp\[Link]" -QueryTimeout 600
foreach ($db in $dblist) { $[Link] }
foreach ($db in $dblist) { Invoke-Sqlcmd -ServerInstance $sqlservername -Database
$[Link] -InputFile "C:\Temp\db_SSB_IE_Permitted_Role.sql" -QueryTimeout 300 }
-- This is how permissions granted to db_SSB_IE_Permitted_Role on VM-DB-PROD-01 and
VM-DB-PROD-02
cls
$sqlservername = 'VM-DB-PROD-02'
$dblist = Invoke-Sqlcmd -ServerInstance $sqlservername -Database master -InputFile
"C:\DatabaseDeployment\[Link]" -QueryTimeout 600
foreach ($db in $dblist) { $[Link] }
foreach ($db in $dblist) { Invoke-Sqlcmd -ServerInstance $sqlservername -Database
$[Link] -InputFile "C:\DatabaseDeployment\Database_DDL_Audit.sql" -QueryTimeout
300 }
$This is how I used text file to read the line for the database connection and
connect to each to execute the script
cls
$sqlservername = 'VM-DB-PROD-02'
foreach($line in [[Link]]::ReadLines("C:\Temp\[Link]"))
{
$line
Invoke-Sqlcmd -ServerInstance $sqlservername -Database $line -QueryTimeout
300 -InputFile "C:\Temp\svcSegmentation_Permissions.sql"
}
$ This is how I deployed handfull of scripts saved under folder
CLS
$sqlservername = 'VM-DB-PROD-01'
$db = 'Aggies'
$folderPath = 'C:\temp\Aggies\'
$ScriptList = Get-ChildItem -Path $folderPath -exclude etl*
foreach ($script in $ScriptList)
{
#$db
#$[Link]
$scriptFullPath = $folderPath + $[Link]
$scriptFullPath
#Invoke-Sqlcmd -ServerInstance $sqlservername -Database $db -InputFile
$scriptFullPath -QueryTimeout 300
# This is how I deployed MDM Pre-release scripts saved under folder on vm-db-dev-01
CLS
$sqlservername = 'VM-NASCAR-DW-P2'
$db = 'MDM'
$folderPath = 'C:\Temp\MDM_2_6\'
$ScriptList = Get-ChildItem -Path $folderPath -exclude etl*
foreach ($script in $ScriptList)
{
#$db
#$[Link]
$scriptFullPath = $folderPath + $[Link]
$scriptFullPath
Invoke-Sqlcmd -ServerInstance $sqlservername -Database $db -InputFile
$scriptFullPath -QueryTimeout 300