cEPRSDBFeatures.psm1
|
enum Ensure { Present Absent } [DscResource()] class cEPRSDBSnapShot { [DscProperty(Key)] [String]$ServerName [DscProperty(Mandatory=$true)] [String]$DatabaseName [DscProperty(Mandatory=$true)] [String]$SnapshotName [DscProperty(Mandatory=$true)] [String]$Logfile [cEPRSDBSnapShot]Get() { Write-Verbose "Getting database connection..." $qdbname = SQLCMD -S $this.ServerName -d $this.DatabaseName -Q "set nocount on; select db_name();" $dbname = $this.DatabaseName $snapshot = $this.SnapshotName $logfilepath = $this.Logfile if($qdbname -Contains $this.DatabaseName) { $dbname = "" } Write-Verbose "Getting detected values..." $returnValue = @{ ServerName = $this.ServerName DatabaseName = $dbname SnapshotName = $this.SnapshotName Logfile = $logfilepath } return $this } [bool]Test() { #Write-Verbose "Use this cmdlet to deliver information about command processing." #Write-Debug "Use this cmdlet to write debug information while troubleshooting." [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMo") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMoExtended") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMoEnum") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null Write-Verbose "Connecting to $($this.ServerName) server- START" | Out-File $this.LogFile -Append $srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$($this.ServerName)" Write-Verbose "Connecting to $($this.ServerName) server- END" | Out-File $this.LogFile -Append Write-Verbose "Checking for existence of DB $($this.DatabaseName) in $($this.ServerName) server- START" | Out-File $($this.Logfile) -Append #$CheckDB=$srvr.Databases | where {$_.name -eq "$DatabaseName"} $database = $srvr.Databases[$this.DatabaseName] Write-Verbose "Checking for existence of DB $($this.DatabaseName) in $($this.ServerName) server- END" | Out-File $($this.Logfile) -Append $DBName=$database.Name Write-Verbose "DB name is $DBName" | Out-File $this.Logfile -Append if($database.Name) { $result = $false } else { Write-Verbose "Database $($this.DatabaseName) doesnot exist" $result = $true } Write-Verbose "$result" return $result } Set() { Write-Verbose "Executing the SQL file on Server: $($this.ServerName) in Database: $($this.DatabaseName)." New-Item -Path $this.Logfile -ItemType File -Force -Verbose Write-Verbose "After File create" CreateDB_Snapshot -ServerName $this.ServerName -DatabaseName $this.DatabaseName -DBSnapshotName $this.SnapshotName -LogFile $this.Logfile } } Function CreateDB_Snapshot { param($ServerName, $DatabaseName, $DBSnapshotName, $LogFile) Try { [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMo") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMoExtended") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMoEnum") | Out-Null [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null Write-Verbose "Connecting to $ServerName server- START" | Out-File $LogFile -Append $srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$ServerName" Write-Verbose "Connecting to $ServerName server- END" | Out-File $LogFile -Append Write-Verbose "Checking for existence of DB $DatabaseName in $ServerName server- START" | Out-File $LogFile -Append $database=$srvr.Databases | where {$_.name -eq "$DatabaseName"} #$database = $srvr.Databases[$DatabaseName] Write-Verbose "Checking for existence of DB $DatabaseName in $ServerName server- END" | Out-File $LogFile -Append $DBName=$database.Name Write-Verbose "DB name is $DBName" | Out-File $LogFile -Append Write-Verbose "------------------------------------------------------------------" | Out-File $LogFile -Append Write-Verbose "Creating '$DBSnapshotName' snapshot on '$DatabaseName' database ." | Out-File $LogFile -Append $snapshot = New-Object ("Microsoft.SqlServer.Management.Smo.Database") ($ServerName,$DBSnapshotName) $snapshot.DatabaseSnapshotBaseName=$database.Name #Add all filegroups from the base database to snapshot foreach ($filegroup in $database.FileGroups) { $newfg = New-Object ("Microsoft.SqlServer.Management.Smo.FileGroup") ($snapshot,$filegroup.Name) Write-Verbose "newfg = $newfg" $snapshot.FileGroups.Add($newfg) } #Add all datafiles from each filegroup of the base database to snapshot with a new datafile name #.ss as the datafile extension for the snapshot file #By default, the snapshot files will reside in the same folder as base DB data files foreach ($filegroup in $database.FileGroups) { foreach ($datafile in $filegroup.Files) { $newDataFile = New-Object ("Microsoft.SqlServer.Management.Smo.DataFile") ($snapshot.FileGroups[$filegroup.Name],$datafile.Name,"$($database.PrimaryFilePath)\$($datafile.Name).ss") $newDataFile.FileName $newDataFilename=$newDataFile.FileName Write-Verbose "newDataFile = $newDataFilename" $snapshot.FileGroups[$filegroup.Name].Files.Add($newDataFile) } } #Create the snapshot Write-Verbose "Creating '$DBSnapshotName' snapshot-START." | Out-File $LogFile -Append $snapshot.Create("$DBSnapshotName") | Out-File $LogFile -Append Write-Verbose "Creating '$DBSnapshotName' snapshot-END." | Out-File $LogFile -Append Write-Verbose "------------------------------------------------------------------" | Out-File $LogFile -Append Write-Verbose "Creating DB Snapshot finished." | Out-File $LogFile -Append } Catch { [System.Exception] Write-Error $_.Exception.Message | Out-File $LogFile -Append } Finally { Write-Verbose "Execution completed!" | Out-File $LogFile -Append } } [DscResource()] class cEPRSRestoreSnapshot { [DscProperty(Key)] [String]$ServerName [DscProperty(Mandatory=$true)] [String]$DatabaseName [DscProperty(Mandatory=$true)] [String]$SnapShotName [DscProperty(Mandatory=$true)] [String]$LogFile [cEPRSRestoreSnapshot]Get() { Write-Verbose "Getting database connection..." $qdbname = SQLCMD -S $this.ServerName -d $this.DatabaseName -Q "set nocount on; select db_name();" $dbname = $this.DatabaseName $snapshot = $this.SnapShotName $logfilepath = $this.LogFile if($qdbname -Contains $this.DatabaseName) { $dbname = "" } $this.DatabaseName = $dbname return $this } [bool]Test() { $result=$false [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$($this.ServerName)" $CheckDB=$srvr.Databases | where {$_.name -eq "$($this.DatabaseName)"} if($CheckDB.Name -ne "$($this.DatabaseName)") { Write-Error "$($this.DatabaseName) Database not found in server $($this.ServerName)!" $result = $true } return $result } Set() { Write-Verbose "Executing the SQL file on Server: $($this.ServerName) in Database: $($this.DatabaseName)." New-Item -Path $this.LogFile -itemtype "file" -Force RestoreDB_Snapshot -ServerName $this.ServerName -DatabaseName $this.DatabaseName -DBSnapshotName $this.SnapShotName -LogFile $this.SnapShotName } } Function RestoreDB_Snapshot { param($ServerName, $DatabaseName, $DBSnapshotName, $LogFile) Try { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$ServerName" $CheckDB=$srvr.Databases | where {$_.name -eq "$DatabaseName"} if($CheckDB.Name) { Write-Verbose "Restoring '$DatabaseName' database by '$DBSnapshotName' snapshot." $db = $srvr.Databases["$DatabaseName"] $SQLScript= "RESTORE DATABASE $DatabaseName FROM DATABASE_SNAPSHOT = '$DBSnapshotName'" $db.ExecuteNonQuery($SQLScript) | Out-File $LogFile -Append Write-Verbose "Restoration finished." } else { Write-Verbose "Database '$DBSnapshotName' does not exists!" | Out-File $LogFile -Append } } Catch { [System.Exception] Write-Error $_.Exception.Message } Finally { Write-Verbose "Execution completed!" } } [DSCResource()] class cEPRSKillDBSessions { [DscProperty(Key)] [String] $ServerName [DscProperty(Key)] [String] $DatabaseName [cEPRSKillDBSessions] Get() { try { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $SQlSvr1 = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$($this.ServerName)" $DesiredRule=$SQlSvr1.Databases | where {$_.name -eq "$($this.DatabaseName)"} $Presence = if($DesiredRule){"Present"}else{"Absent"} $output = @{ Ensure = $Presence; ServerName = $this.ServerName; DatabaseName = $this.DatabaseName; } return $output } Finally { $this.DesiredRule.Dispose() } } [bool] Test() { $Result = $false [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $SQlSvr1 = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$($this.ServerName)" Write-Verbose "$($this.ServerName); $($this.DatabaseName)" -Verbose $DesiredRule=$SQlSvr1.Databases | where {$_.name -eq "$($this.DatabaseName)"} $Presence = if($DesiredRule){"Present"}else{"Absent"} if($Presence -eq $this.Ensure) { $Result = $false } else { $Result = $true } return $Result } [void] Set() { try { # Killing the existing DB sessions [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $SQlSvr1 = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$($this.ServerName)" $SQlSvr1.KillAllprocesses("$($this.DatabaseName)") } Catch [system.exception] { Write-Verbose $_.exception.message -Verbose } } } [DSCResource()] class cEPRSRestoreDBFromBackup { [DscProperty(Key)] [String] $SQLServer [DscProperty(Mandatory = $true)] [String] $SQLDatabase [DscProperty(Mandatory = $true)] [String] $BakfilePath [DscProperty(Mandatory = $true)] [bool] $TrustedConnection [DscProperty(Mandatory = $false)] [String] $SQLusername [DscProperty(Mandatory = $false)] [String] $SQLpassword [cEPRSRestoreDBFromBackup] Get() { Write-Verbose "Getting database connection..." $qdbname = SQLCMD -S $this.SQLServer -d $this.SQLDatabase -Q "set nocount on; select db_name();" $dbname = $this.SQLDatabase $backfile = $this.BakfilePath if($qdbname -Contains $this.SQLDatabase) { $dbname = "" } $returnValue = @{ ServerName = $this.SQLServer DatabaseName = $dbname BackupFile = $backfile } return $this } [bool] Test() { $result=$false [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$($this.SQLServer)" $CheckDB=$srvr.Databases | where {$_.name -eq "$($this.SQLDatabase)"} $BakExists = Test-Path "$($this.BakfilePath)" -ErrorAction SilentlyContinue if($BakExists -eq $true) { $result = $false } else { $result = $true } return $result } Set() { Write-Verbose "Restoring Database $($this.SQLDatabase) on $($this.SQLServer) from $($this.BakfilePath)" Restore-SQLdatabase -SQLServer $this.SQLServer -SQLDatabase $this.SQLDatabase -BakfilePath $this.BakfilePath -TrustedConnection $this.TrustedConnection -SQLusername "$($this.SQLusername)" -SQLpassword $this.SQLpassword } } Function Restore-SQLdatabase { param( $SQLServer, $SQLDatabase, $BakfilePath, $TrustedConnection, $SQLusername, $SQLPassword ) Try { if ($TrustedConnection -eq $false) { if($SQLUsername -eq "") { write-warning "The SQL Username variable must be defined, if 'TrustedConnection' is false. Use '-SQLusername' to define username." break } if($SQLPassword -eq "") { write-warning "The SQL Password variable must be defined, if 'TrustedConnection' is false. Use '-SQLPassword' to define username." break } } $SQLConn = New-Object System.Data.SQLClient.SQLConnection #checks for a trusted SQL connection if($TrustedConnection -eq $false) { $UserNameSplit = $SQLusername.split("\") $UserName = $UserNameSplit[1] $Domain = $UserNameSplit[0] $Testcredentials=ValidateUserCredentials -Domain $Domain -Username $UserName -Password $SQLPassword if($Testcredentials.IsValid -eq "False") { Write-Error "User credentials are not correct. Please check the values provided." exit -1 } #Using an SQL account for login $SQLConn.ConnectionString = "Data Source=$SQLServer;User ID=$SQLusername;password=$SQLpassword;Initial Catalog=master;Integrated Security=True;Trusted_Connection=True;" } Else { #Using a trusted connection $SQLConn.ConnectionString = "Server=$SQLServer; Trusted_Connection=True" } Write-verbose "Attempting to connect to the Specified SQL server:" try{ $SQLConn.Open() Write-verbose "Success" } catch{ Write-warning "An exception was caught while attempting to open the SQL connection, please confirm the login details are correct and try again." Break } $SQLCmd = New-Object System.Data.SQLClient.SQLCommand $SQLcmd = $SQLconn.CreateCommand() $sqlcmd.commandtimeout=0 $SQLcmd.CommandText="ALTER DATABASE $SQLDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE $SQLDatabase FROM DISK = '$BakfilePath' WITH REPLACE" $starttime = Get-date try{ $SQLcmd.Executenonquery() | out-null $result="Success" Write-Verbose "Database restoration finished!" } catch{ write-warning "An Exception was caught while restoring the database!" write-warning "$_" write-warning "attempting to recover the database" $SQLcmd.CommandText="ALTER DATABASE $SQLDatabase SET MULTI_USER" $SQLcmd.Executenonquery() | out-null $result="Failed" } finally{ $SQLconn.close() $timetaken=[math]::round(((get-date) - $starttime).totalseconds,0) $report=new-object PSObject -Property @{ SQLServer=$SQLserver; Database=$sqlDatabase; Result=$result; Timetaken="$timetaken Seconds";} } Return $Report } Catch { [System.Exception] Write-Verbose $_.Exception.Message } Finally { Write-Verbose "Execution completed!" } } Function ValidateUserCredentials { param($Domain, $Username, $Password) Try { Add-Type -AssemblyName System.DirectoryServices.AccountManagement $ct = [System.DirectoryServices.AccountManagement.ContextType]::Domain $pc = New-Object System.DirectoryServices.AccountManagement.PrincipalContext($ct, $Domain) New-Object PSObject -Property @{ UserName = $Username; IsValid = $pc.ValidateCredentials($Username, $Password,"Negotiate").ToString() } } Catch { [System.Exception] Write-Host $_.Exception.Message } Finally { "User credentials validation finished." [System.Console]::WriteLine("User credentials validation finished.") } } [DSCResource()] class cEPRSExecuteSql { [DscProperty(Key)] [String] $ServerName [DscProperty(Key)] [String] $DatabaseName [DscProperty(Key)] [String] $SQLFile [DscProperty(Key)] [String] $Logfile [DscProperty()] [String] $Variables [cEPRSExecuteSql] Get() { Write-Verbose "Getting database connection..." -Verbose $qdbname= SQLCMD -S $this.ServerName -d $this.DatabaseName -Q "set nocount on; select db_name();" $dbname=$this.DatabaseName $sqlfilepath=$this.SQLFile $logfilepath=$this.Logfile if($qdbname -Contains $this.DatabaseName) { $dbname = "" } Write-Verbose "Getting SQL file path..." -Verbose if(-not(Test-Path -Path "$($this.SQLFile)" -Include *.sql)) { $sqlfilepath = "" } Write-Verbose "Getting detected values..." -Verbose $returnValue = @{ ServerName = $this.ServerName DatabaseName = $dbname SQLFile = $sqlfilepath Logfile = $logfilepath } return $returnValue } set() { Write-Verbose "Executing the SQL file on Server: $($this.ServerName) in Database: $($this.DatabaseName)." New-Item -Path $this.Logfile -itemtype "file" -Force $executable = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe' if($this.Variables -eq '' -or $this.Variables -eq $null) { Write-Verbose "if part" $arglist = "SQLCMD.EXE -S $($this.ServerName) -d $($this.DatabaseName) -E -b -i `"$($this.SQLFile)`" | Out-File -FilePath `"$($this.Logfile)`"" Write-Verbose "arglist: $arglist" $proc = Start-Process $executable -ArgumentList "$arglist" -Wait -NoNewWindow -PassThru if($proc.HasExited) { Write-Verbose "SQLCMD.EXE execution process exited." } } else { Write-Verbose "else part" Write-Verbose "Variables: this.Variables." $arglistVar = "SQLCMD.EXE -S $($this.ServerName) -d $($this.DatabaseName) -E -b -i `"$($this.SQLFile)`" -v $($this.Variables) | Out-File -FilePath `"$($this.Logfile)`"" Write-Verbose "arglistVar: $arglistVar" $proc = Start-Process $executable -ArgumentList "$arglistVar" -Wait -NoNewWindow -PassThru if($proc.HasExited) { Write-Verbose "SQLCMD.EXE execution process exited." } } Write-Verbose "Execution completed. Examine the log file, $($this.Logfile)" } [bool] Test() { $result = $false Write-Verbose "Verifying execution..." -Verbose if(Test-Path -Path "$($this.Logfile)") { $wordsToFind = {"error","fail","aborted","rolled back","stopped"} $log = Get-Content $this.Logfile foreach($word in $wordsToFind) { $containsWord = $log | %{$_ -like $word} } If($this.containsWord -contains $true) { Write-Verbose "SQL file failed to execute successfully. Examine the log file, $($this.Logfile)" return $false } } Write-Verbose "Verification completed. ...." -Verbose Write-Verbose "$result" -Verbose return $result } } |