sql server 2008 还原数据库（自动获取日期最大的备份文件）

--获取文件夹地址

declare @filepath varchar(500) = 'E:\Backup_SQL_Server'

declare @TableName varchar(128) = 'DataTransfer.dbo.IIS_Log_OriGeneUS_Daily_temp'

declare @query varchar(1000)

declare @query_Bulk_Insert varchar(1000)

declare @max1 int

declare @count1 int = 0

Declare @filename varchar(100)

set @count1 =0

create table #x (name varchar(200))

set @query = 'master.dbo.xp_cmdshell "dir '+ @filepath +' /b"'

--print @query

EXEC sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE WITH OVERRIDE;

insert #x exec (@query)

EXEC sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', 0;RECONFIGURE WITH OVERRIDE;

delete from #x where name is NULL

select identity(int,1,1) as ID, name into #y from #x

drop table #x

declare @name varchar(500)

select @name=max(name) from #y where name like 'broadleaf_ZSBio%.bak'

 

--获取日期最大的备份文件

set @name ='E:\Backup_SQL_Server\'+@name

 

--还原数据库

RESTORE DATABASE [broadleaf_ZSBio]

FROM  DISK = @name

WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

drop table #y

GO