I have a MSSQL 2008R2 DB in SIMPLE recovery mode that is periodically backed up with
BACKUP DATABASE MyDB to DISK = 'Z:\MyDB.cbak' WITH COMPRESSION
This runs periodically, creating multiple backups sets in the file. I want to restore the latest backup that was made.
Doing this by hand I can run
RESTORE HEADERONLY from Disk='Z:\DougHub.cbak' and then find the latest position to run
RESTORE DATABASE MyDB from Disk='Z:\MyDB.cbak' with FILE = <some number> but I want to automate this step such that it always restores the most recent backup.
How do I create a TSQL command that will restore the most recent backup from the backup file?