SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
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 sqlserver2000
-- Modified By:   maggiefengyu@tom.com --- 01/17/2007 sqlserver2005
--
-- 调用举例: exec sp_RestoreDir 'F:\backup_data\db_test', 'E:\sqlserver_data\db_test'
/***************************************************************************************/

ALTER     proc [dbo].[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 nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileId bigint,
CreateLSN numeric(25,0), 
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
 )

--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  

delete from #dirList where filename is null

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

begin  
     declare BakFile_csr cursor for 
  	 select filename from #dirList where id>1 and id<158 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 + "'" 
--        select @cmd
       	insert #filelist exec ( @cmd )
       	
--     	select * from #filelist

    	if right(@filename,3)='trn'
    	begin
       	select @dbName = left(@filename,datalength(@filename) - patindex('%_pukcab_%',reverse(@filename))-7)

       	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('%_pukcab_%',reverse(@filename))-7)

       	  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 and @filename is not null
	BEGIN
-- 如果恢复成功,记恢复操作日志
	   delete from restore_log where dt<getdate()-2
    	   insert into restore_log (filename,dt) values (@filename,getdate())
        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 ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO