if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RestoreDir]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





/***************************************************************************************/
-- 目    的: 根据某个目录下某个数据库的备份文件(*.trn或*.bak都可以)还原数据库.
--           配合xcopy命令，可以在另一台备份机器上实现standby SQL Server数据库. 
--
-- 输入参数: @restoreFromDir - 存放*.trn或*.bak数据库备份文件的目录
--	     @restoreToDataDir - 数据库数据文件将要还原的目录
--	     @restoreToLogDir - 数据库日志文件将要还原的目录, 如果为空,日志文件和数据文件目录相同
--
-- Written By:    Chris Gallelli -- 8/22/2003
-- Modified By:   Bruce Canaday -- 11/04/2003
-- 		  http://www.sqlservercentral.com/scripts/contributions/962.asp
-- Modified By:   maggiefengyu@tom.com --- 02/23/2005
--
-- 调用举例: exec sp_RestoreDir 'F:\backup_data\db_test', 'E:\sqlserver_data\db_test'
/***************************************************************************************/

CREATE   proc sp_RestoreDir 
       @restoreFromDir varchar(255),
       @restoreToDataDir varchar(255)= null,
       @restoreToLogDir varchar(255) = null
as

--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
	set @restoreToLogDir = @restoreToDataDir

set nocount on

declare @filename         varchar(40),
	@cmd              varchar(500), 
        @DataName         varchar (255),
	@LogName          varchar (255),
        @LogicalName      varchar(255), 
	@PhysicalName     varchar(255), 
	@Type             varchar(20), 
	@FileGroupName    varchar(255), 
	@Size             varchar(20), 
	@MaxSize          varchar(20),
	@restoreToDir     varchar(255),
	@DBName           varchar(255),
        @PhysicalFileName varchar(255),
        @i_exist	  int 

create table #dirList (id [int] IDENTITY (1, 1) NOT NULL , filename varchar(100))
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )

--Get the list of database backups that are in the restoreFromDir directory order by date desc
   select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

set @i_exist=0
insert into #dirList(filename) exec master..xp_cmdshell @cmd  

-- 找到备份目录下次新的2个文件名, 不处理最新的, 避免最新备份好的物理文件没有完全复制成功
 select filename from #dirList where id>1 and id<8 order by id desc

begin  
     declare BakFile_csr cursor for 
  	 select filename from #dirList where id>1 and id<8 order by id desc
end

open BakFile_csr

fetch BakFile_csr into @filename

while @@fetch_status = 0
   begin

--  判断恢复日志表restore_log存在否，不存在则创建表 
   	if not exists (select * from dbo.sysobjects where id = object_id('restore_log') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	begin
	   create table restore_log (filename varchar(128),dt datetime default getdate())
	end

--  判断此备份文件有无在恢复日志表里记录过?
    	select @i_exist=count(0) from restore_log where filename=@filename
    	if @i_exist=0
    	begin

-- 根据备份目录下的物理文件名, 找到对应的逻辑名等参数
        select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'" 

       	insert #filelist exec ( @cmd )
       	
--     	select * from #filelist

    	if right(@filename,3)='TRN'
    	begin
       	select @dbName = left(@filename,datalength(@filename) - patindex('%_golt_%',reverse(@filename))-5)

       	select @cmd = "RESTORE Log " + @dbName + 
		" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH STANDBY='"+@restoreToDataDir+"\UNDO_"+@filename+".DAT '," 

       	print '' 
       	print '--RESTORING Log  ' + @dbName
    	end
    
    	if right(@filename,3)='BAK'
    	begin
          select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)

       	  select @cmd = "RESTORE DATABASE " + @dbName + 
		" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH NORECOVERY ," 

       	  print '' 
          print '--RESTORING DATABASE ' + @dbName
    	end    
    
-- 找到数据库逻辑和物理文件名称之间的对应关系
       	declare DataFileCursor cursor for  
		select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
		from #filelist

       	open DataFileCursor
       	fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

       	while @@fetch_status = 0
          begin
                 -- RESTORE with MOVE option 
		       select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 )) 
			select @restoreToDir = @restoreToDataDir
			
       		select @cmd = @cmd + 
            		" MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', " 
                 
              fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

          end  -- DataFileCursor loop

	close DataFileCursor
    	deallocate DataFileCursor

       	select @cmd = @cmd + ' REPLACE'
     	print @cmd 		
     	print '' 
     	
     	select @cmd
     	
       	EXEC (@cmd)

	IF @@ERROR=0
	BEGIN
-- 如果恢复成功,记恢复操作日志
	   delete from restore_log where dt<getdate()-2
    	   insert into restore_log (filename) values (@filename)
        END

       	truncate table #filelist
       	
       end -- @i_exist=0

     fetch BakFile_csr into @filename

   end -- BakFile_csr loop

close BakFile_csr
deallocate BakFile_csr

drop table #dirList

return




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO