Automatic restore database to different server from backup file

Use Master

/****** Object:  StoredProcedure [dbo].[sp_RestoreDir]    Script Date: 12/5/2014 3:06:04 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

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


/***************************************************************************************/
CREATE proc [dbo].[sp_1RestoreFullandtran] 
    @restoreFromDir varchar(255),
@onlyFUllbakup Char(1)='Y',
@ServerName  varchar(20)='ServerA',
@moveto        char(1)='N',
    @restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null
as

set nocount on
/*
Author David Zhou
Date Dec 10 2014


    @restoreFromDir  backup file location
@onlyFUllbakup  folder only full backup or full backup plus transaction back in the same folder
@moveto         Move to different folder, if Y, Must input @restoreTodataDir Paramenter value
@ServerName  if those script will run on ServerA, just put serverA-- it is just for future build mirror 

    @restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null

exec sp_1RestoreFullandtran  @restoreFromDir ='c:\worrk',@onlyFUllbakup='N',@ServerName ='ServerA'-- restore full backup and transaction database with same location

exec sp_1RestoreFullandtran @restoreFromDir ='c:\worrk',@onlyFUllbakup='N',@ServerName ='ServerA',@moveto='Y',@restoreToDataDir='d:\data',@restoreToLogDir='D:\log-- restore full backup and transaction database with same location

*/

if @moveto='Y' and @restoreToDataDir is null

begin
  print ' you move to paramenter is yes, you need input move to folder informaiton'
  return

end

declare @filename         varchar(200),
 @cmd              varchar(1000), 
 @cmd2             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),
        @searchName       varchar(255),
 @DBName           varchar(255),
        @PhysicalFileName varchar(255),
@DBExist   int=0,
@newDbanema varchar(255)



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[restorMirrorDB]'))
 truncate table [dbo].[restorMirrorDB]


 else
 Create table [dbo].[restorMirrorDB] (DBname varchar(100),Sqlstatement varchar(1000),flagmirror int,ServerName varchar(20))




create table #dirList (filename varchar(200))

create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
                                    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
                                    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID  varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

 print ' --This restore script will run on ' + @ServerName  
 print ' '

If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir



--Get the list of database backups that are in the restoreFromDir directory

   select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'


insert #dirList exec master..xp_cmdshell @cmd  

--select * from #dirList where filename like '%_backup_%' --order by filename

if @onlyFUllbakup='Y'
   declare BakFile_csr cursor for 
  select * from #dirList where filename like '%_backup_%bak' order by filename
else
   begin  -- single db, don't order by filename, take default latest date /o-d parm in dir command above
    
     declare BakFile_csr cursor for 
   select * from #dirList where (filename like '%_backup_%bak') or (filename like '%_backup_%trn') order by filename
 end




open BakFile_csr
fetch next from BakFile_csr into @filename

while @@fetch_status = 0
   begin
        



   select @dbName =substring(@filename,0,charindex('_backup_',@filename,0) )

     select @DBExist =count(*) from [dbo].[restorMirrorDB] where DBname=@dbName
   
 

 
       select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'" 

   insert #filelist exec ( @cmd )
   -- PRINT '' 
  -- PRINT 'RESTORING DATABASE ' + @dbName
   
         
   if @DBExist =0

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

   declare DataFileCursor cursor for  
select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
from #filelist

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

while @@fetch_status = 0
  begin
    
  if @Moveto = 'Y'  
   begin 
select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 )) 

       if @Type = 'L'
       select @restoreToDir = @restoreToLogDir
       else
       select @restoreToDir = @restoreToDataDir
       
       select @cmd = @cmd + 
            " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', " 
  end 
 
  fetch next from DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

   end  -- DataFileCursor loop

close DataFileCursor
     deallocate DataFileCursor

 select @cmd = @cmd  + '  NORECOVERY, REPLACE'
   end 


else  if @DBExist > 0
   begin
       select @cmd = "RESTORE Log  " + @dbName + 
" FROM DISK = '" + @restoreFromDir + "\" + @filename  +"'   WITH "
  select @cmd = @cmd  + '  NORECOVERY'
       end   
    
    
 
  insert into [dbo].[restorMirrorDB](dbname,sqlstatement,flagmirror,ServerName) select @dbName,@cmd,1,@ServerName
   print @cmd
   print ''

      
        truncate table #filelist

      fetch next from BakFile_csr into @filename
 end -- BakFile_csr loop

close BakFile_csr
deallocate BakFile_csr

drop table #dirList

return

GO