Seeking help using / combining two Powershell scripts

evolart · March 11, 2014, 1:59am

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: Restore database backup files in a folder to SQL server using PowerShell | Obligatory Moniker
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)

}

}

evolart · March 11, 2014, 4:37am

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 <code>&quot;&quot;+$DisplayName.DisplayName+&quot;</code>" /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 “”

donj · March 11, 2014, 7:27am

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?

evolart · March 12, 2014, 4:52am

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.

donj · March 12, 2014, 4:54am

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.

Topic		Replies	Views
Restoring SQL databases with multiple data files fails PowerShell Help	1	144	December 15, 2016
SQL Backup using Powershell PowerShell Help	3	150	January 11, 2016
Backup sql Storedproc with powershell PowerShell Help	4	211	April 27, 2020
Converting .bak file format to VHd using Powershell PowerShell Help	4	195	May 5, 2020
Combine Get-ChildItem and Test-Path PowerShell Help	2	178	November 16, 2020

Seeking help using / combining two Powershell scripts

BEGIN OF FOR LOOP

END OF FOR LOOP

Related Topics