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

  • 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"

    }

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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