What I'm trying to achieve is finding out how long retention a database backup has by using DATEDIFF function. But in order to use DATEDIFF I would need something to compare, the data from the result, because I don't know it being anywhere else.
Why from a result ?
I found out that this command gives me all the info I need to accomplish my task (BackupFinishDate, ExpirationDate):
RESTORE HEADERONLY FROM DISK = 'X:\Backups\Backuptest.bak'
I'm pretty sure I'm not allowed to create temp tables in production servers, so if this is one option, I'm afraid I can't use that.
PS! If there's a better way to find out retentiondays of a backup, I'd happily use that. If this would be possible in PowerShell, that would be even better.
Well, it seemed I answered my own question with the PowerShell hint.. I gave myself :P
Solution was:
$bkp_start = Invoke-Sqlcmd -ServerInstance myServer -Query "RESTORE HEADERONLY FROM DISK = 'X:\Backups\Backuptest.bak'" | Select-Object -ExpandProperty BackupFinishDate
$bkp_end = Invoke-Sqlcmd -ServerInstance myServer -Query "RESTORE HEADERONLY FROM DISK = 'X:\Backups\Backuptest.bak'" | Select-Object -ExpandProperty ExpirationDate
$RetentionInDays = New-TimeSpan -Start $bkp_start -End $bkp_end | Select-Object -ExpandProperty Days
Write-Output "Retention period : $RetentionInDays"
I love PowerShell.. looks a bit clunky, but it works and I don't know a better way at this time.