Store invoke-sqlcmd result in PowerShell variable and use it for comparison

Question

Store invoke-sqlcmd result in PowerShell variable and use it for comparison

MillionQueries

Old Hand

Points: 367
More actions
February 6, 2022 at 7:44 pm

#3980983
Hello,
I was trying to store invoke-sqlcmd result in a PowerShell variable but it seems that I am doing something wrong because $dbsize variable in the below code does not store the value from the invoke-sqlcmd. I would appreciate if I can get some help with it.
param
(
[string]$ServerName,
[string[]]$databaseName
)
$date = Get-Date -Format yyyyddMM
$servername='abcd'
$databaseName='testdb'
$sqlusername='test'
$sqlpassword='P@ssword**'
foreach ($db in $databaseName)
{
$dbsizequery="USE [master] select str(convert(dec (14,2) ,size) / 128,14,2) as DATABASE_SIZE_MB from sys.master_files as mf INNER JOIN sys.databases as da ON da.database_id = mf.database_id where DB_NAME (mf.database_id)='$db' and type_desc='Rows'"
$dbsize=invoke-sqlcmd -ServerInstance $server -Database master -username $sqlusername -Password $sqlpassword -Query $dbsizequery -AS DataSet
$dbsize.Tables[0].Rows | %{ echo "{ $($_['DATABASE_SIZE_MB'])}" }
}
If ($dbsize -le 20000.00)
{
$sql="USE [master]`
BACKUP DATABASE $db TO DISK = N\'$path\'+\'$ServerName\'_\'$db\'_\'$date'\_1.bak`
,WITH COPY_ONLY,`
NAME = N\'$db'\`
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS=10;`
GO"
invoke-sqlcmd -ServerInstance $server -Database Master -username $sqlusername -Password $sqlpassword -Query $sql
Write-Host "Backing up Database into 1 partition"
}
- This topic was modified 2 years, 3 months ago by MillionQueries.

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

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

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

I can't help here but I have to tell you that putting passwords into clear text is a really bad idea. They really need to be "trusted" connections based on whomever the current user is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 2

You can get all backup / restore history from msdb database!

have a look at ....

select  BU.server_name
      , BU.machine_name
      , BU.database_name
      , BU.name as BUName
      , BU.backup_start_date
      , BU.backup_finish_date
      , case BU.[TYPE]
          when 'D' then 'Full'
          when 'I' then 'Diff'
          when 'L' then 'Log'
          when 'F' then 'File or filegroup'
          when 'G' then 'Diff file'
          when 'P' then 'Partial'
          when 'Q' then 'Diff partial'
          else '???'
        end as BuType
      , CAST(BU.backup_size / 1024 / 1024 as decimal(18, 3)) as backup_size_MB
 , dateadd(ss, datediff(ss, BU.backup_start_date, BU.backup_finish_date ), '1900-01-01')  ElapsSS
      , CAST(BU.compressed_backup_size / 1024 / 1024 as decimal(18, 3)) as COMPRESSED_BU_size_MB
      , BU.position
 , BU.First_LSN
 , BU.Last_LSN
 , BU.Checkpoint_LSN
 , BU.Database_backup_LSN
      , BU.[description]
      , BU.recovery_model
      , BU.[user_name]
      , BU.expiration_date
      , BMF.physical_device_name 
from    msdb.dbo.backupset BU
inner join msdb.dbo.backupmediaset BS
on BS.media_set_id = BU.media_set_id 
inner join msdb.dbo.backupmediafamily BMF
on BMF.media_set_id = BU.media_set_id

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

I'll add to that... look at what you're doing... You're writing T-SQL in PowerShell to do backups? Just write a stored procedure and then you don't have to worry about privs or where to put the PowerShell scripts or exposing passwords or scheduling the run, etc, etc. And, if you make every machine responsible for its own backups, you won't have the problem of your transaction log files exploding overnight if your "central" backup system hurls.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 4

Instead of reinventing the wheel - why not download dbatools from https://dbatools.io/ and use them instead? Not sure why you are only backing up the database if the size is less than some value - looks like you are trying to stripe the backup if the size gets to a certain limit, but with compressed backups I am not sure that is buying you anything.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs