Search and Restore .bak File [BULK RESTORE], SQL Server

You can say this process as BULK Restore of .bak file.

Using the below script we can search and restore bak files; Just we need to follow some pattern.

Make sure the .bak file names start with DB name and some pattern.

Below script will search the bak file from disk/provided path and restore the file.

Follow the below steps to prepare the environment.

1. Create a Table [dbo].[dbNames] in [master] database.

CREATE TABLE [dbo].[dbNames](  [ID] [int] IDENTITY(1,1) NOT NULL,  [DBNAME] [nvarchar](max) NULL,  [DBFILENAME] [nvarchar](max) NULL,  [DBFILELOCATION] [nvarchar](max) NULL,  [Comment] [nvarchar](500) NULL,  CONSTRAINT [PK_dbNames] PRIMARY KEY CLUSTERED ([ID] ASC)  )

2. Insert Database names which need to restore and leave [Comment] column blank

INSERT INTO [dbo].[dbNames] ([DBNAME], [DBFILENAME], [DBFILELOCATION]) values ('Test_Database','FileName','FileLocation') Example SQL select d.name,m.name,m.physical_name from sys.databases d JOIN sys.master_files m ON d.database_id = m.database_id where d.name not in ( 'master','tempdb','model','msdb','DBAdmin')

3. Make sure all .bak files name start with the Database name and some static words ie. "_Backup_2019"

Test_Database_Backup_20190131_01.bak
Test_Database2_Backup_20190131_01.bak

4. Now Adjust the below Script and Run. wow ... all databases are ready to use!!

declare @dbName varchar(MAX) ='' declare @fileName varchar(MAX) declare dbNamecursor cursor for select distinct DBNAME from dbNames Where Comment is null DECLARE @DataPath nvarchar(500), @DBFilePath nvarchar(500), @DBLogPath nvarchar(500) SET @DataPath = '<BAK file location>' SET @DBFilePath = '<DB file (.mdf) location>\' SET @DBLogPath = '<DB LOG file (.ldf) location>\' DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT,IS_FILE bit) INSERT INTO @DirTree(subdirectory, depth,IS_FILE) EXEC master.sys.xp_dirtree @DataPath,0,1 declare @createSQL varchar(MAX) ='' declare @createMOVESQL varchar(MAX) ='' OPEN dbNamecursor FETCH NEXT FROM dbNamecursor INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN set @fileName = null select @fileName=a.subdirectory from @DirTree a where a.subdirectory like @dbName+'_Backup_FULL_20190924%.bak' SET @createSQL = '' SET @createMOVESQL='' if @fileName is not null begin print char(10)+'@dbName = '+@dbName+' --> @fileName = '+@fileName set @createSQL = 'restore database ['+@dbName+ '] from disk = '''+@DataPath+'\'+@fileName+''' ' select @createMOVESQL = @createMOVESQL + ' , MOVE '''+s.DBFILENAME+''' TO '''+case when s.DBFILELOCATION like '%ldf' then @DBLogPath+DBNAME+'_log' else @DBFilePath+DBNAME end+'_'+ cast ( ROW_NUMBER() over (order by s.DBFILENAME) as varchar) +right(s.DBFILELOCATION,4)+''' ' from dbNames s where  DBNAME=@dbName set @createSQL= @createSQL+ ' WITH '+right(@createMOVESQL,len(@createMOVESQL)-2) print @createSQL exec(@createSQL) update dbNames set Comment='Done' where DBNAME=@dbName end else begin print char(10)+'@dbName = '+@dbName+' --> @fileName = NOT FOUND' update dbNames set Comment='FILE NOT FOUND' where DBNAME=@dbName end FETCH NEXT FROM dbNamecursor INTO @dbName END CLOSE dbNamecursor; DEALLOCATE dbNamecursor;