Powershell Syntax

Question

Powershell Syntax

mitzyturbo

Hall of Fame

Points: 3452
More actions
August 15, 2012 at 10:23 am

#266008

Hi guys
I raised a similar thread on this some time ago and have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular):
http://sysadmingrunt.blogspot.ie/
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
Any help is appreciated on this, if you need anymore info just let me know

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mitzyturbo (8/15/2012)
Hi guys
I raised a similar thread on this some time ago and have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular):
http://sysadmingrunt.blogspot.ie/
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
Any help is appreciated on this, if you need anymore info just let me know

Making it easier for others to check your reference:

http://sysadmingrunt.blogspot.ie/

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 2

The issue is down to this function:

function RedgateGetDatabaseName ([STRING]$backupfile)

{

$SQLConnection.Open()

$SQLQuery = "exec master..sqlbackup '-sql ""RESTORE SQBHEADERONLY FROM DISK = [$backupfile] WITH SINGLERESULTSET""'"

$SQLCommand = New-Object system.Data.SqlClient.SqlCommand

$SQLCommand.CommandText = $SQLQuery

$SQLCommand.Connection = $SQLConnection

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SQLAdapter.SelectCommand = $SQLCommand

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SQLConnection.Close()

return $DataSet.Tables[0].Rows[12] | Select-Object 'SQL Backup v6.5.1.9'

}

The call to the function is in the foreach loop:

$filename = $filename.FullName

# Get Database Name

$dbName = RedgateGetDatabaseName -backupfile $filename

$dbName = $dbName[1..$dbName.Length]

$dbName = $dbName[0].'SQL Backup v6.5.1.9'.Replace("Database name : ", "")

When I echo out the $dbname after the first line I get a 20 and a blank line. If I echo out the $DataSet within the function itself it gives me a 20 and the full database name ie Database name : XXXXX. The 20 would appear to be the number of rows returned from the sp call exec master..sqlbackup '-sql ""RESTORE SQBHEADERONLY FROM DISK = [$backupfile] WITH SINGLERESULTSET. I've tried setting the SET NOCOUNT before the execute is ran but it still doesn't work.

If I run the sp itself within SSMS with SET NOCOUNT ON it returns the query result with no number of rows returned in the Messages tab.

Error message I receive:

You cannot call a method on a null-valued expression.

At B:\restoresp.ps1:173 char:55

+ $dbName = $dbName[0].'SQL Backup v6.5.1.9'.Replace <<<< ("Database name: ", "")

+ CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 3

Managed to figure it out, different versions of Red Gate between environments meant the Replace failed

FrankDux2015 Grasshopper Points: 15 More actions · Answer 4

mitzyturbo (8/16/2012)
Managed to figure it out, different versions of Red Gate between environments meant the Replace failed

Hello mitzyturbo - I know this thread is a few years old but it was one of the only references I can find online for this issue. I too am having this experience and was hoping that I could get a little more insight into how you fixed your issue. I changed my version to match what my current version of RedGate SQLBackup and am still getting the null value error mentioned in this thread.

Thanks!

mitzyturbo Hall of Fame Points: 3452 More actions · Answer 5

Not sure Frank, it was quite sometime ago and it was in a different company so I don't have access to the stuff I was using then.

Best I can remember is that you have to specifically state the version of RedGate you're using i.e. 'SQL Backup v6.5.1.9' within the code declaration. Depending on what you state here, Powershell will look for the necessary definition on the server you're running the script on.

If you don't state the correct version of RedGate you're using it won't work.

If you're using 2008 R2 and above, you could probably find a more apt powershell script which uses SQL backups, i.e. .BAK files as opposed to using red gate