Last Restore Details for SQL

In a previous entry, I posted T-SQL code that will list the last restore dates for all db's on a SQL Server. The original purpose was to verify the dates that production backups were restored to the test environment.

This enhanced script will also display the name of the MDF file and the name & location of the backup file that was used for the restore. This can serve as a double-check that the dev servers are running the latest copies from production.

CREATE PROC [dbo].[procLastRestoreDetails]
(
@database_name AS varchar(50) = NULL
)
AS

SELECT
BS.database_name,
RH.restore_date AS Last_Restored_DateTime,
RF.destination_phys_name AS Restored_To_Database_Location,
BMF.physical_device_name AS Restored_From_Backup_File
FROM
msdb.dbo.restorehistory RH
INNER JOIN msdb.dbo.restorefile RF
ON RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb.dbo.backupset BS
ON RH.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id = BMF.media_set_id
WHERE
RF.destination_phys_name LIKE '%.mdf'
AND RH.backup_set_id =
(
SELECT
MAX(backup_set_id)
FROM
msdb.dbo.restorehistory
WHERE
destination_database_name = BS.database_name
)
AND
(
BS.database_name = @database_name
OR
@database_name IS NULL
)
ORDER BY
BS.database_name

Access File Size

This function will return the size of the current Access database file. This function requires a reference to the Microsoft Scripting Runtime library, usually located at:

C:\WINDOWS\system32\scrrun.dll

An Access mdb file can grow over time, especially if you have a lot of edits, empty / refill temp tables, and so forth. The purpose of this function is to get the current size, which (using other code not shown here) is then compared against some arbitrary value, and then alert the user if Compact and Repair is recommended to improve performance.


Function GetMyFileSize() As Long
Dim fso As FileSystemObject
Dim oFolder As Folder, oFile As File
'
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(CurrentProject.Path)
Set oFile = oFolder.Files(CurrentProject.Name)
GetMyFileSize = oFile.Size
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing
End Function