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