Automated dB Restore
Here is the SP:
CREATE procedure ala_RestoreTestDB as
declare @filename as char(200)
declare filename_cursor cursor for
select top 1 substring(message, 22, len(message) - 22) as filename
where database_name = 'ALAGP'
and activity = 'Backup database'
and succeeded = 1
order by sequence_id desc
fetch next from filename_cursor into @filename
RESTORE DATABASE TEST
FROM DISK = @filename
MOVE 'GPSALAGPDat.mdf' TO 'E:\GPSQLData1\SQLData\GPSTESTDat.mdf',
MOVE 'GPSALAGPLog.ldf' TO 'E:\GPSQLLogs1\SQLLogs\GPSTESTLog.ldf'
As you can see, it takes the last good backup from the sysdbmaintplan_history table in msdb and restores it to a database named test. I would like to use this sp to do the same thing with SQL Backup. It is backup history stored in a table somewhere? If not, any other suggestions?