Seeking help using / combining two Powershell scripts

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 3 years, 1 month ago.

  • Author
    Posts
  • #13681
    Profile photo of Chris Smith
    Chris Smith
    Participant

    I'm a complete Powershell newb here so apologies for that upfront. I'm trying to restore multiple SQL bak files on a daily basis using Powershell and found this script: http://obligatorymoniker.wordpress.com/2013/02/26/restore-database-backup-files-in-a-folder-to-sql-server-using-powershell/#comment-54
    Our BAK files are all compressed as GZ files and sent to a clone of the server. I'd like to use the script above in conjunction with this script I've pieced together to extract the GZ files containing the SQL backups. It looks like you pass the directory of your BAK files to the second script listed here but I'm not sure of the syntax. I have left a message on the blog as well. Any help is greatly appreciated!

    Script to extract GZ compressed BAK files:


    $ZipFiles = Get-ChildItem D:\RestorefromDenver\Extracted -Recurse -Include *.gz

    $ZipFiles.count | out-default

    foreach ($ZipFile in $ZipFiles)

    {

    C:\7z.exe e -y -oD:\RestorefromDenver\Extracted $ZipFile.Name

    }

    Here is the whole SQL restore script from the ObligatoryMoniker blog:


    function invoke-DatabaseRestore {
    param ([String]$SQLServer="(local)", $BackupPath, [String]$BackupFileFilter= "")
    #load assemblies
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") |Out-Null

    gci $BackupPath -Filter $BackupFileFilter | select fullname | % {
    $backupFile = $_.FullName

    #we will query the database name from the backup header later
    $server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" )$SQLServer
    $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($backupFile, "File")
    $smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" )
    $backupDevice| FL *

    #Get default log and data file locationshttp://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx
    $DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) {$server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath }
    $LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) {$server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath }

    #restore settings
    $smoRestore.NoRecovery = $false;
    $smoRestore.ReplaceDatabase = $true;
    $smoRestore.Action = "Database"
    $smoRestore.PercentCompleteNotification = 10;
    $smoRestore.Devices.Add($backupDevice)

    #get database name from backup file
    $smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

    #display database name
    "Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"]

    #give a new database name
    $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]

    #Relocate each file in the restore to the default directory
    $smoRestoreFiles = $smoRestore.ReadFileList($server)

    foreach ($File in $smoRestoreFiles) {
    #Create relocate file object so that we can restore the database to a different path
    $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile" )

    #the logical file names should be the logical filename stored in the backup media
    $smoRestoreFile.LogicalFileName = $File.LogicalName

    $smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L") {$LogPath} else {$DataPath} ) + "\" + [System.IO.Path]::GetFileName($File.PhysicalName)
    $smoRestore.RelocateFiles.Add($smoRestoreFile)
    }
    #restore database
    $smoRestore.SqlRestore($server)

    }
    }

  • #13684
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Couple of notes –

    Avoid replying to your own posts; a lot of us look for "unanswered posts" and by replying, you come off that list and don't get noticed.

    Consider attaching (as a TXT file) long scripts like that. It's a bit much to digest in a web page.

    In terms of fixing the orphaned users, that's more a SQL question. Are you asking for the relevant T-SQL query, or how to execute that from PowerShell?

    • #13704
      Profile photo of Chris Smith
      Chris Smith
      Participant

      Thanks for the heads up Don I will follow those practices in the future. As far as the orphaned users I decided to take the easy way out and just run an existing SQL script in conjunction with the PowerShell script above. Hopefully I will learn enough from your CBT Nuggets Powershell Ultimate Course to know how to write my own scripts in the near future.

  • #13705
    Profile photo of Don Jones
    Don Jones
    Keymaster

    And to set an expectation, that's exactly the kind of thing you SHOULD be running a SQL script for. The vast majority of admin things you'd want to do with SQL can be done with a query or T-SQL script, which is one reason the SQL team never built a huge set of PowerShell cmdlets for their product. Using PowerShell to kick of a T-SQL script is completely legit.

  • #13682
    Profile photo of Chris Smith
    Chris Smith
    Participant

    Alright I've got this portion working now by combining a couple of other scripts. I now need to figure out how to fix all of the orphaned users after the DB restores are complete.

    I call everything using:

    sqlps.exe -executionpolicy bypass -File D:\Scripts\SQLRestores.ps1 -BackupFileLocation D:\RestorefromDenver\Extracted -SqlServerName na-alb-prdsql1

    The current script is:

    Param (
    [string]$BackupFileLocation,
    [string]$SqlServerName
    )

    ########### Extract GZ files containing SQL BAK files #################################################

    $shell=new-object -com shell.application

    $CurrentLocation=get-location

    $CurrentPath=$CurrentLocation.path

    $Location=$shell.namespace($CurrentPath)

    $ZipFiles = Get-ChildItem D:\RestorefromDenver\Extracted -Recurse -Include *.gz

    $ZipFiles.count | out-default

    foreach ($ZipFile in $ZipFiles)

    {
    C:\7z.exe e -y -oD:\RestorefromDenver\Extracted $ZipFile.Name
    }

    ########### Setup for BAK file restores #################################################

    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SqlServerName
    $DatabaseDefaultPath= $server.MasterDBPath +"\"

    $bakFiles = Get-ChildItem $BackupFileLocation -Filter "*.bak"

    ### BOS Get the Default File Locations ###

    $DefaultSystemDataLocation = $Server.Information.MasterDBPath
    $DefaultSystemLogLocation = $Server.Information.MasterDBLogPath

    $DefaultUserDBDataLocation = $Server.Settings.DefaultFile
    $DefaultUserDBLogLocation = $Server.Settings.DefaultLog

    if ($DefaultUserDBDataLocation.Length -eq 0)
    {
    $DefaultUserDBDataLocation = $DefaultSystemDataLocation
    }
    if ($DefaultUserDBLogLocation.Length -eq 0)
    {
    $DefaultUserDBLogLocation = $DefaultSystemLogLocation
    }

    ### EOS Get the Default File Locations ###

    ###### BEGIN OF FOR LOOP ##############################################################################

    foreach ($bak in $bakFiles)
    {

    $RestoreHeaderOnlyQuery="Restore HeaderOnly From Disk=N'"+$bak.FullName+"'"
    $HeaderData=INVOKE-SQLCMD -SERVERINSTANCE $server -QUERY $RestoreHeaderOnlyQuery

    $FileNumber=0

    foreach ($HData in $HeaderData)
    {
    If($HData.BackupType -eq 1)
    {
    $FileNumber=$HData.Position
    $DBName=$HData.DatabaseName
    $BackupOfServer= "["+$HData.ServerName+"]"
    }
    }

    if ($FileNumber -ne 0)
    {

    IF($DBName -eq "master" -OR $DBName -eq "model" -OR $DBName -eq "msdb")
    {
    $DefaultDataLocation=$DefaultSystemDataLocation+ "\"
    $DefaultLogLocation=$DefaultSystemLogLocation+ "\"
    }
    else
    {
    $DefaultDataLocation=$DefaultUserDBDataLocation+ "\"
    $DefaultLogLocation=$DefaultUserDBLogLocation+ "\"
    }

    $db = $server.Databases[$DBName]

    write-host "————————————————————-"
    write-host "Restoring database [$($dbName)] On Server $($server)" -ForegroundColor Green
    write-host "————————————————————-"

    $RestoreFileListQuery="Restore FilelistOnly From Disk=N'"+$bak.FullName+"'"
    $FileListData=INVOKE-SQLCMD -SERVERINSTANCE $server -QUERY $RestoreFileListQuery
    $MoveQuery=""

    foreach ($FData in $FileListData)
    {

    $PhName=$FData.PhysicalName
    $str= $PhName.SubString(0,$PhName.LastIndexOf("\")+1)
    $PhName=$PhName -replace "\\","_"
    $str=$str -replace "\\","_"
    $LogicalFilename= $PhName -replace $str,""

    IF($FData.Type -eq "D")
    {
    $MoveQuery=$MoveQuery+
    "MOVE N'"+$FData.LogicalName+"'"+
    "TO N'"+$DefaultDataLocation+ $LogicalFilename+"',"
    }

    IF($FData.Type -eq "L")
    {
    $MoveQuery=$MoveQuery+
    "MOVE N'"+$FData.LogicalName+"'"+
    "TO N'"+$DefaultLogLocation+ $LogicalFilename+"',"
    }
    }

    $RestoreQuery= "Restore Database "+ $DBName +
    " FROM DISK=N'"+$bak.FullName+"' "+
    "WITH REPLACE,FILE = "+ $FileNumber+","+
    $MoveQuery +
    "NOUNLOAD, STATS = 20"

    if ($DBName -eq "master")
    {

    if($BackupOfServer -eq $server)
    {
    $QrForInstance="select isnull('mssql?'+cast(SERVERPROPERTY('instancename') as varchar(50)),'mssqlserver') as InstanceName"
    $Instance=INVOKE-SQLCMD -SERVERINSTANCE $server -QUERY $QrForInstance -VERBOSE
    $ServiceName=$Instance.InstanceName
    $DisplayName= Get-Service -name $ServiceName | select DisplayName
    $Command="net start ""+$DisplayName.DisplayName+"" /m"

    If($ServiceName -like "*sql*" -and $DisplayName -like "*sql*")

    {

    #Stop Service
    stop-service $ServiceName -force -verbose

    #Start Service in Single User Mode
    invoke-EXPRESSION $Command -verbose

    #Restoring Master Database
    write-host "********************"
    INVOKE-SQLCMD -SERVERINSTANCE $server -QUERY $RestoreQuery -querytimeout 65534 -VERBOSE
    write-host "********************"
    write-host ""

    #Stop Service
    stop-service $ServiceName -force -verbose

    #Start Service
    start-service $ServiceName -verbose

    }
    }
    else
    {
    write-host "You are restoring The backup of master database of server $($BackupOfServer) to Server $($server), which is not compatiable." -ForegroundColor Red
    write-host "Please restore valid backup." -ForegroundColor Red

    }

    }
    else
    {
    if ($db -ne $null)
    {
    $server.KillAllProcesses($dbName)
    }

    INVOKE-SQLCMD -SERVERINSTANCE $server -QUERY $RestoreQuery -querytimeout 65534 -VERBOSE
    }

    write-host "————————————————————-"
    write-host ""

    }
    ELSE
    {
    write-host "———————————————————————————————————–"
    write-host "Backup file $($bak.FullName) does not contain any FULL database backup(s)" -ForegroundColor Red
    write-host "———————————————————————————————————–"
    write-host ""

    }
    Start-Sleep -Seconds 3

    }

    ###### END OF FOR LOOP ##############################################################################

    write-host ""

You must be logged in to reply to this topic.