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