如何自动还原最新的备份数据库？
在 e:\db_bk下有多个完全备份的数据库(如：backup_201307260230.bak等)，希望编写SQL脚本自动查找目录下最新的bk文件，然后还原，每天6点自动还原。
[解决办法]
写个SP，动态产生恢复指令就OK啦
[解决办法]
create table #t
(id int not null identity(1,1), filenames varchar(500))
insert into #t(filenames)
exec master..xp_cmdshell 'dir /b/s e:\db_bk\*.bak'
declare @filename varchar(500),@tsql varchar(6000)
select @filename=max(replace(replace(filenames,'e:\db_bk\',''),'.bak',''))+'.bak'
from #t
where filenames is not null
select @tsql='restore database [数据库名] from disk=''e:\db_bk\'+@filename+''' with replace '
exec(@tsql)
drop table #t
USE msdb
GO
SELECT physical_device_name
FROM backupmediafamily
WHERE media_set_id = (
SELECT MAX(media_set_id)
FROM backupset
WHERE database_name = 'data'
AND backupset.[type] = 'D'
)
