Powershell

All replies

• 

  

  0

  Sign in to vote

  Please post the portion of the PS script that is generating the error, and a description of what it is doing

  ---

  22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

  • Kevin3NF on Twitter
  • Dallas DBAs
  • Why is my Log file so big?

  Tuesday, July 18, 2017 3:16 PM
• 

  

  0

  Sign in to vote

  Hi Ranjith,

  How is that SSIS related?

  ---

  Arthur
  
  MyBlog
  
  Twitter

  Tuesday, July 18, 2017 3:18 PM
• 

  

  0

  Sign in to vote

  ##########################################
  # This Script requires:
  #  1) Currently, this script depends on creating a local administrator user named 'deployUser' on the DB machine
  #  2) the user 'deployUser' with same password to be created as a SQL Server login with 'sysadmin' rights
  #  3) to avoid query/script timeout errors, you must modify the registry settings:
  #       a) log in to DB server as the 'deployUser'
  # b) run 'regedit'
  # c) change the HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\SQLDB\Database\QueryTimeoutSeconds 'decimal' value from '60' to at least '360' AND/OR
  # d) change the HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database\QueryTimeoutSeconds 'decimal' value from '60' to at least '360'
  #       e) reboot the server to make settings effective
  #######
  
  ##########################################
  ### Parsing Command-line Arguments
  param(
  [string]$databaseName           # specifies the name of the database that will be installed as configured in the environment .xml - MATCHES TO AN EXISTING '<databasename>.publish.sql' FILE!
  )
   
  # Using Powershell (x86) Run as Administrator ensuring that the Windows PowerShell Execution Policies are unrestricted and that the SQLPS Add-In for PowerShell has been imported
  $PublishSQLRelativePath ="..\..\db\dacpac\DacPac\"
  ; $PublishSQLFileName = "$databaseName"+".publish.sql"
  ; $ServerName = "."
  ; $SourceDBName = "$databaseName"+"_Temp_New_DB" #TempDB Name
  ; $TargetDBName = "$databaseName"
  ; $TargetDBName2 = "$databaseName"
  ; $WorkFolder = "C:\Windows\Temp\"  
  ; $PreDeployParam = "declare @DefaultData nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output
  declare @DefaultLog nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output
  declare @MasterData nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
  select @MasterData=substring(@MasterData, 3, 255)
  select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))
  declare @MasterLog nvarchar(512)
  exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
  select @MasterLog=substring(@MasterLog, 3, 255)
  select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
  DECLARE @DefaultDataPath VARCHAR(200) = (select isnull(@DefaultData, @MasterData) DefaultData)
  DECLARE @DefaultLogPath VARCHAR(200) = ( select isnull(@DefaultLog, @MasterLog) DefaultLog )
  
  :setvar DatabaseName "
  Copy-Item $PublishSQLRelativePath$PublishSQLFileName $WorkFolder"TEMP-"$PublishSQLFileName  -PassThru | Set-ItemProperty -name isreadonly -Value $false
  
  ###Update the publish.sql file to run Pre-Pre Deployment Parameters
  function replaceCmdletParameterValueInFile( $file, $key, $value ) {
      $content = Get-Content $file
      if ( $content -match  ":setvar\s*$key" ) {
          $content -replace ":setvar\s*$key", $value |
          Set-Content $file     
      }
  }
  $scriptfile = $WorkFolder+"TEMP-"+$PublishSQLFileName
  replaceCmdletParameterValueInFile $scriptfile "DatabaseName" "$PreDeployParam"
  
  ###Update the publish.sql file to utilize target file paths
  function replaceCmdletParameterValueInFile( $file, $key, $value ) {
      $content = Get-Content $file
      if ( $content -match  ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-\s]*[\'\""_]") {
          $content -replace ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-\s]*[\'\""_]", ":setvar $key $value" |
          Set-Content $file     
      } else {
          Add-Content $file ":setvar $key $value"
      }
  }
  $scriptfile = $WorkFolder+"TEMP-"+$PublishSQLFileName
  replaceCmdletParameterValueInFile $scriptfile "DefaultDataPath" "@DefaultData"
  replaceCmdletParameterValueInFile $scriptfile "DefaultLogPath" "@DefaultLog";
  
  # add path for SQLPackage.exe
  IF (test-path "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe")
  { $env:path =  ";C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;" }
  ELSEIF
  (test-path "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe")
  { $env:path =  ";C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin;" }
  ELSEIF
  (test-path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe")
  { $env:path =  ";C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin;" }
  ELSE
  {  "Cannot find SQLPackage"}
   
  #check database exists on server
  if ($TargetDBName = Invoke-Sqlcmd -Query "IF db_id('$TargetDBName') IS NOT NULL 
  BEGIN 
  Select '$TargetDBName' 
  END 
  IF db_id('$TargetDBName') IS  NULL 
  BEGIN 
  CREATE DATABASE [$TargetDBName] 
  END
  "-ServerInstance "$ServerName")
  {
  
  #If database exists then compate and upadate
  WRITE '#...DB Already Exists.. Comparing and Updating Database';
  
  ###Update the publish.sql file to utilize the TargetDBName
  function replaceCmdletParameterValueInFile( $file, $key, $value ) {
      $content = Get-Content $file
      if ( $content -match ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]" ) {
          $content -replace ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]", ":setvar $key $value" |
          Set-Content $file     
      } else {
          Add-Content $file ":setvar $key $value"
      }
  }
  $scriptfile = $WorkFolder+"TEMP-"+$PublishSQLFileName
  replaceCmdletParameterValueInFile $scriptfile "DatabaseName" "`"$SourceDBName`""
  replaceCmdletParameterValueInFile $scriptfile "DefaultFilePrefix" "`"$SourceDBName`"";
  
  Invoke-Sqlcmd -Query "IF db_id('$SourceDBName') IS  NULL 
  BEGIN  
  CREATE DATABASE [$SourceDBName] 
  END"-ServerInstance "$ServerName";
  
  
  ###Execute the publish.sql script to create the temporary SourceDB
  Invoke-Sqlcmd -InputFile $WorkFolder"TEMP-"$PublishSQLFileName -ServerInstance "$ServerName";
  
  ###Export the temporary SourceDB as a dacpac
  sqlpackage /a:extract /of:true /scs:"server=$ServerName;database=$SourceDBName;trusted_connection=true" /tf:$WorkFolder$SourceDBName".dacpac";
  
  ###Compare the  temporary SourceDB to TargetDB and create the change report
  sqlpackage.exe /a:deployreport /op:$WorkFolder"report.xml" /of:True /sf:$WorkFolder$SourceDBName".dacpac" /tcs:"server=$ServerName; database=$TargetDBName;trusted_connection=True" /p:BlockOnPossibleDataLoss=True /p:DropObjectsNotInSource=FALSE
  [xml]$x = gc -Path $WorkFolder"report.xml";
  $x.DeploymentReport.Operations.Operation |
  % -Begin {$a=@();} -process {$name = $_.name; $_.Item | %  {$r = New-Object PSObject -Property @{Operation=$name; Value = $_.Value; Type = $_.Type} ; $a += $r;} }  -End {$a};
  
  ###Compare the temporary SourceDB to TargetDB and create the change sql script
  sqlpackage.exe /a:script /op:$WorkFolder"change.sql" /of:True /sf:$WorkFolder$SourceDBName".dacpac" /tcs:"server=$ServerName; database=$TargetDBName;trusted_connection=True" /p:BlockOnPossibleDataLoss=True /p:DropObjectsNotInSource=FALSE;
  
  ###Excute changes to the TargetDB
  Invoke-Sqlcmd -InputFile $WorkFolder"change.sql";
  
  ###Drop the temporary SourceDB
  Invoke-Sqlcmd -Query "USE [master] 
  GO 
  EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'$SourceDBName'
  GO 
  USE [master] 
  GO 
  DROP DATABASE [$SourceDBName]"-ServerInstance "$ServerName"  ;
  }
  ELSE
  {
  #If DB does not exists then create new database
  WRITE '#...DB Does Not Exist... Creating New Database ' ;
  
  ###Update the publish.sql file to utilize the TargetDBName
  function replaceCmdletParameterValueInFile( $file, $key, $value ) {
      $content = Get-Content $file
      if ( $content -match ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]" ) {
          $content -replace ":setvar\s*$key\s*[\',\""][\w\d\.\:\\\-]*[\'\""_]", ":setvar $key $value" |
          Set-Content $file     
      } else {
          Add-Content $file ":setvar $key $value"
      }
  }
  $scriptfile = $WorkFolder+"TEMP-"+$PublishSQLFileName;
  replaceCmdletParameterValueInFile $scriptfile "DatabaseName" "`"$TargetDBName2`""
  replaceCmdletParameterValueInFile $scriptfile "DefaultFilePrefix" "`"$TargetDBName2`"";
  
  ###Execute the publish.sql script to create the TargetDB
  Invoke-Sqlcmd -InputFile $WorkFolder"TEMP-"$PublishSQLFileName -ServerInstance "$ServerName" ;
  
  }

  Tuesday, July 18, 2017 7:30 PM
• 

  

  0

  Sign in to vote

  Hi,

  Powershell script will deploy the database( create new database)  if not exist,

  If already exist the same database it will compare with DACPAC schema and existing database schema

  then update it if any new changes came up with DACPAC script, That's powershell script job  function.

  They integrated with SSIS package for create DACPAC.

  I hope you understand.

  Thank You

  Tuesday, July 18, 2017 7:37 PM
• 

  

  1

  Sign in to vote

  Where is this part: 'INCREMENTAL'?

  ---

  Arthur
  
  MyBlog
  
  Twitter

  Tuesday, July 18, 2017 10:00 PM
• 

  

  0

  Sign in to vote

  I don't find this keyword 'INCREMENTAL' in script, But i don't know why it's showing error.

  Wednesday, July 19, 2017 3:11 PM
• 

  

  0

  Sign in to vote

  I don't find this keyword 'INCREMENTAL' in script, But i don't know why it's showing error.

  Has to be there, computers don't lie :-)

  This is the key to resolving this issue.

  ---

  Arthur
  
  MyBlog
  
  Twitter

  Wednesday, July 19, 2017 5:20 PM