Query to variable - not wanting table style result

Question

Post reply

Query to variable - not wanting table style result

Drenlin

Ten Centuries

Points: 1099
More actions
December 4, 2012 at 8:00 am

#265736

Hi
I am trying to run a script that queries the last full backup file for a specific database. Invoke line formated for ease of reading.
# Script is ran on the SQL server (not remotely)
# Instance is called sql-instance
# database is called sql-data
import-module SQLPS -disableNameChecking
$instanceName = "$($env:computername)\sql-instance"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$lastBackupFile = Invoke-Sqlcmd -Query "
SELECT TOP 1 msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D' and msdb.dbo.backupset.database_name = 'sql-data'
ORDER BY msdb.dbo.backupset.backup_finish_date desc"
The result I get with this is in a table format:
physical_device_name
--------------------
C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSQL\Backup\full_sql-data_20121101.bak
Is there any way to just get the result and not the heading stuff?
I need just the path and filename for using elsewhere in the script.
I tried searching around but only found articles that give the table format and I don't know enouth to trim this down with confidence.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

If it's one result, you can get it like this:

$lastBackupFile.physical_device_name

This can be used in other methods, e.g.:

($lastBackupFile.physical_device_name).SubString(0, 30)

If it's multiple rows you can do something similar but include the row number:

$lastBackupFile[0].physical_device_name

Drenlin Ten Centuries Points: 1099 More actions · Answer 2

Many thanks for that.

It must seem like common sense to you but I didn't realize that queries would actually return results with members that can be seperated. This opens up a world of possibility and extra knowledge for me.

Query resolved.

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

No problem.

Still getting to grips with Powershell and very little of it is common sense so far 🙂

The hardest bit has been linking all the individual steps together - once you start seeing how you can pass info from one step to the next it really does become an amazing tool!

Cheers

Gaz

JackG Mr or Mrs. 500 Points: 540 More actions · Answer 4

This is the closest I've come to finding a topic on what I'm trying to do. I'm setting a PS variable equal to the results from a query that returns two values per row and there will be multiple rows returned. Here is the PS code:

$SQL = "SELECT DBName, SFTPFolder FROM db.Export.ExportDBs"

$Database = SQLCMD -S "server" -U "User" -P "password" -Q $sql

Using the information above I figured out that $Database[2] would give me my first result row since 0 and 1 were the headers for the result set. I'm trying to use the results in a FOREACH. I'd like to put the DBName in one variable and the SFTPFolder in a different variable within the FOREACH. I tried $Database[2].DBName but it doesn't return the dbname, at least when I'm playing with it from the PS command line. So I'm a bit at a loss. Any help would be most appreciated.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Hi Jack, try using Invoke-SqlCmd in place of SQLCMD - it gives the results in a data row format, whereas I think SQLCMD just gives you a string that you won't be able to loop through.

You can pipe the results straight into a foreach loop, or use the variable like so:

$SQL = "SELECT DBName, SFTPFolder FROM db.Export.ExportDBs"

Invoke-SqlCmd -Serverinstance "server" -Username "User" -Password "password" -Query $sql | `

foreach {code block - use $_.DBName and $_.SFTPFolder to access the values}

or

$SQL = "SELECT DBName, SFTPFolder FROM db.Export.ExportDBs"

$Database = Invoke-SqlCmd -Serverinstance "server" -Username "User" -Password "password" -Query $sql

foreach ($d in $Database)

{

code block - use $d.DBName and $d.SFTPFolder to acces the values

}

Hope that helps.

Cheers

Gaz

JackG Mr or Mrs. 500 Points: 540 More actions · Answer 6

Awesome! Thanks Gazareth!

Now that I've read your post I vaguely remember hearing that invoke-sqlcmd returns the table and sqlcmd returns a string. There's no way I would have pulled that nugget of information out of the dusty recesses of my brain though. 😛 Thanks again.

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

No worries mate 🙂

If in doubt, you can check the type & properties of something by piping it to get-member:

$Database | get-member