I wonder if you can help me! I've searched the great answers of above and Google and alas i have been unable to find the answer to my conundrum!
Essentially I am running a SQL command from within PowerShell to retrieve the last restore Date Time however it is not displaying in a way that I (I think) cannot compare to the current date to see if the restore is consistent.
The script running is as per below:
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SERVER\Instance;Database=DataBaseName;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "DECLARE @DB sysname = 'DataBaseName';
SELECT TOP 1 restore_date
WHERE destination_database_name = @DB
ORDER BY restore_date DESC;"
$SqlCmd.Connection = $SqlConnection
$dbrestoredate = $SqlCmd.ExecuteScalar()
Write-Output "Database Restore date " $dbrestoredate
You could use
select TOP 1 CONVERT(char(10), restore_date, 103) from msdb.dbo.restorehistory
This will return a string representing the date in the requested format (
Please note that using
TOP x without using
ORDER BY is non-deterministic, since the order of the rows returned by the query can not be guaranteed unless using the
order by clause.