Tuesday, August 26, 2014

Restoration of N number of backup files at a time

-- Using below script you can restore all databases backup file in single shot backup files should be in C:\Backupfiles location. And backup file stored in below format
--- C:\Backupfiles\Database1\Database1_Timestamp.bak  
--- this script consider Database1 as database name.


-- C:\Backupfiles  : Backup location
--- Backup file location should be like C:\Backupfiles\Database1\Database1_Timestamp.bak

Declare @MinVal Int
Declare @MaxVal   Int
Declare @InLp_MinVal   Int
DECLARE @whichFile VARCHAR(1000);
Declare @backupfilename Varchar(100)
Declare @cmdfolder Varchar(100)
Declare @cmdfilename Varchar(100)
Declare @databasename  Varchar(100)
Declare @SqlString nvarchar(4000)
Declare @restorefile varchar(100)

CREATE TABLE #DBNameAndBakFile (SLNo Int Identity, DBName VARCHAR(100) , BackupFileName Varchar(100) );
Create table #Dboutput (SLNo Int Identity,Bkupfilename varchar(100));
create table #DatabaseRestored (Databasename Varchar(100))

Set NoCount On;
                                
Select @MinVal =1,
                                @MaxVal = 1,
                                @InLp_MinVal = 1
                               
                Select @cmdfolder = 'dir "C:\Backupfiles" /ad /b' --- This is the backups locaiton
                  
                INSERT into #DBNameAndBakFile (DBName) 
                EXEC   master.dbo.xp_cmdshell @cmdfolder  -- This will store the DATABASE NAMES into temp table #DBNameAndBakFile
               
                Select @MaxVal = MAX(SlNo) from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
               
                While @MinVal <= @MaxVal
                                Begin
                                               
                                                Select @databasename = Dbname from #DBNameAndBakFile where Slno = @MinVal ;
                                                Select @cmdfilename = 'dir ' + '"C:\Backupfiles\' + @databasename +'" /b /o:gn | sort' 
                                    
                                    Insert into #Dboutput 
                                    EXEC master.dbo.xp_cmdshell @cmdfilename
                                    
                                    
                                   Update A set BackupFileName = B.Bkupfilename 
                                   From #DBNameAndBakFile  A, #Dboutput B
                                   Where (A.SlNo  =  B.Slno Or A.SLNo = @MinVal)  And (B.Bkupfilename Is Not Null Or  B.Bkupfilename<>'')
           
           Delete from #Dboutput
          
            
                                   select @MinVal = Min(SlNo) from #DBNameAndBakFile Where SlNo > @MinVal ;
                                  
                                End 
                
                                -- Select * from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
               
                                Print '<Restoration Scripts Starts Here......>'
                               
                                Declare @Rst_MinVal Int
                                Declare @Rst_MaxVal Int
               
                                Select @Rst_MinVal = Min(SlNo) from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
                                Select @Rst_MaxVal = MAX(SlNo) from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
               
                                
                
                                While @Rst_MinVal <= @Rst_MaxVal
                                Begin
                                               
                                                Select @databasename = Dbname, @restorefile =BackupFileName  from #DBNameAndBakFile where Slno = @Rst_MinVal ;
                                                --Select  @databasename as Dbnaes, @restorefile as Restorefile
                                                Print '<Restoration for the database ......>'
                                                Print @databasename
                                                Insert into #DatabaseRestored values(@databasename )
                                                                               
                                                Select @SqlString  = 'RESTORE DATABASE ['+ @databasename + '] FROM 
                                                DISK = N'''+ 'C:\Backupfiles\'+ @databasename +'\' + @restorefile
                                                + ''' WITH  FILE = 1,  NOUNLOAD,REPLACE,  STATS = 10'
                                               
                                                Exec sp_executesql        @SqlString
                                                --Print @SqlString

                                   select @Rst_MinVal = Min(SlNo) from #DBNameAndBakFile Where SlNo > @Rst_MinVal ;
                                  
                                End 
                                
                                
                
                Drop table #DBNameAndBakFile
                Drop table #Dboutput
                Select * from #DatabaseRestored
                Drop table #DatabaseRestored