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