I ran into a small problem where I needed to automate a restore of my last full backup to a different database. Since my full backup file names include a date/time stamp I needed to query msdb for the backup name. Since I figured it out, I thought I’d post it so I would know where of find it the next time I need it, and maybe help someone else out in the process.
declare @bksource varchar(100)
select @bksource=(
select a.physical_device_name
from [msdb].[dbo].[backupmediafamily] a join [msdb].[dbo].[backupset] b on b.media_set_id = a.media_set_id
where b.backup_start_date =
(
select max( backup_start_date ) from [msdb].[dbo].[backupset]
where database_name = b.database_name and type = ‘D’
)
and database_name = ‘<database_name>’
)
– print @bksource
restore database [<Database_Name2>] from disk = @bksource with replace, stats = 10,
MOVE ‘<Database_Name>’ TO ‘i:\mssql\data\test\<Database_Name2>.MDF’,
MOVE ‘<Database_Name>_log’ TO ‘j:\mssql\xlog\test\<Database_Name2>_log.LDF’
GO
Replace anything inside the <> with the proper names and set the correct drive letters.
Tags: SQL Server, t-sql