Powershell backup-sqldatabase -ExpirationDate

ti1 embracon 0 Reputation points
2023-01-25T15:28:27.3733333+00:00

Greetings!

I'm just having a bad time trying to set the expiration date to a SQL Server backup through powershell SQLSever Module.

My need is that the next backup overwrites the current file in the directory.

I've Tried to achieve that with:

$Expira = (get-date).AddDays(1)

Backup-SqlDatabase -ServerInstance $Instancia -Database $Banco -BackupFile $NomeArquivo -Credential $Credential -BackupAction Database -CompressionOption On -ExpirationDate $Expira -Initialize

It returns 'invalid value to EXPIREDATE param'.

I've tried so many ways of date formatting and can't find anything usefull in forums. The documentation also doesn't presents any example and just says the value for 'ExpirationDate' is a <DateTime>.

The command without -ExpirationDate param works just fine.

PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
118 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Andreas Baumgarten 66,156 Reputation points MVP
    2023-01-25T16:24:13.1+00:00

    Hi @ti1 embracon ,

    with the (get-date).AddDays(1) you get the date and time informationen.

    I am not 100% sure if Backup-SqlDatabase is able to work with the time information.

    You can try this to get the date information only:

    $Expira = (get-date).AddDays(1).Date
    Backup-SqlDatabase -ServerInstance $Instancia -Database $Banco -BackupFile $NomeArquivo -Credential $Credential -BackupAction Database -CompressionOption On -ExpirationDate $Expira -Initialize
    

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards

    Andreas Baumgarten


  2. Limitless Technology 10,251 Reputation points
    2023-01-26T16:57:07.26+00:00

    Hi. Thank you for your question and reaching out. I’d be more than happy to help you with your query.

    To set the expiration date for a SQL Server backup using PowerShell's SQL Server module, you can use the 'Restore-SqlDatabase' cmdlet and include the '-ExpirationDate' parameter with the desired date.

    Here is an example command:

    Restore-SqlDatabase -ServerInstance "YourServerName" -Database "YourDBName" -BackupFile "C:\Path\To\YourBackup.bak" -ExpirationDate "1/1/2022"

    This command will restore the database "YourDBName" from the backup file "C:\Path\To\YourBackup.bak" and set its expiration date to January 1, 2022.

    If the reply was helpful, please don’t forget to upvote or accept as answer, thank you.

    No comments