Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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

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

Using 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 below steps to prepare the invironment..

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

CREATE TABLE [dbo].[dbNames](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DBNAME] [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] ) values ('Test_Database')

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) ='dev_sp2013_devweb39_devweb39CTP_01'
declare @fileName varchar(MAX)

declare dbNamecursor cursor for select DBNAME from dbNames order by ID

DECLARE @DataPath nvarchar(500)
SET @DataPath = '\\dce-p-fps02.epsnet.wan\sql_nonprod_backup\DEVSQL03'

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

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_2019%'

if @fileName is not null
begin
print char(10)+'@dbName = '[email protected]+' --> @fileName = '[email protected]
declare @createSQL varchar(MAX) = 'restore database ['[email protected]+
'] from disk = ''\\dce-p-fps02\sql_nonprod_backup\DEVSQL03\'[email protected]+''''
--print @createSQL
exec(@createSQL)

update dbNames
set Comment='Done'
where [email protected]
end

else

begin
print char(10)+'@dbName = '[email protected]+' --> @fileName = NOT FOUND'
update dbNames
set Comment='FILE NOT FOUND'
where [email protected]
end

FETCH NEXT FROM dbNamecursor
INTO @dbName

END

CLOSE dbNamecursor;
DEALLOCATE dbNamecursor;




This post first appeared on Nothing Is Bug Free, please read the originial post: here

Share the post

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

×

Subscribe to Nothing Is Bug Free

Get updates delivered right to your inbox!

Thank you for your subscription

×