How to restore database (daily) with multiple bak files using a T-SQL script?

Question

I am trying to restore a database on a daily basis from multiple bak files that I get from a vendor.

I tried using GUI, and it worked.

I basically followed the same procedure as shared on the this link.

But, I need to do this on a daily basis with new BAK files that I receive.

I get these six bak files, and I would like to run a script to run once a day to refresh whole data in that database.

enter image description here

I tried something like on the bottom:

I got the script from this link:

USE Master; 
GO 
SET NOCOUNT ON 
-- 1 - Variable declaration 
DECLARE @dbName sysname
DECLARE @fileName sysname
DECLARE @standby sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
-- 2 - Initialize variables 
SET @dbName = 'WH_BEE' 
SET @fileName = 'WH_BEE_' 
SET @backupPath = 'F:\NHFilesToday\' 
SET @standby = 'F:\backup\WH_BEE.bak' 
-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 
-- 4 - Find latest diff backup 
---SELECT @lastDiffBackup = MAX(backupFile) 
---FROM @fileList 
---WHERE backupFile LIKE '%.DIF' 
 ---AND backupFile LIKE @dbName + '%' 
 ---AND backupFile > @lastFullBackup 
-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR 
 SELECT backupFile 
 FROM @fileList 
 WHERE backupFile LIKE '%.bak' 
 AND backupFile LIKE @fileName + '%' 
OPEN backupFiles 
-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = ''' 
 + @backupPath + @backupFile + ''' WITH STANDBY='''+ @standby +'''' 
 
 FETCH NEXT FROM backupFiles INTO @backupFile 
EXEC (@cmd)
END 
CLOSE backupFiles 
DEALLOCATE backupFiles 
-- 6 - put database in a useable state 
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY' 
PRINT @cmd

I got this error:

enter image description here

I am not sure these bak files are considered log backup files or something else..

Do I have to delete database and restore database?

I need some help with fixing the script or is there any simpler script I could use?

Update (8/18/2024):

I got this error after I tried the replaced part 5 (leaving all other parts (1,2,3 & 6) same from the original).

enter image description here

This is what the original db looks like (normal status):

enter image description here

This is where mdf and ldf files are located:

enter image description here

I am not sure where the standby file is/should be located:

I just created this line (without actually this file exists).

I am not sure what to do with this line of the script.

SET @standby = 'F:\backup\WH_BEE.bak' 

Update (8/19/2024):

This is generated script:

USE [master]
RESTORE DATABASE [WH_BEE] FROM 
DISK = N'F:\NH\WH_BEE_20240819_1.bak', 
DISK = N'F:\NH\WH_BEE_20240819_2.bak', 
DISK = N'F:\NH\WH_BEE_20240819_3.bak', 
DISK = N'F:\NH\WH_BEE_20240819_4.bak', 
DISK = N'F:\NH\WH_BEE_20240819_5.bak', 
DISK = N'F:\NH\WH_BEE_20240819_6.bak' WITH FILE = 1, 
MOVE N'WH_Data1' TO N'F:\data\WH_BEE.MDF', 
MOVE N'WH_Data2' TO N'F:\data\WH_BEE_1.NDF', 
MOVE N'WH_Log' TO N'G:\log\WH_BEE.LDF', NOUNLOAD, STATS = 5
GO

• nbk: see if this helps stackoverflow.com/questions/18360666/…
• Yano_of_Queenscastle: "I tried using GUI, and it worked." FYI: If you can prepare the desired action in SSMS, instead of executing it by clicking OK, you can often ask SSMS to script the action for you. Then tweak the generated T-SQL script as necessary. imgur.com/a/CrQehhD
• Java: @Yano_of_Queenscastle Thank you so much. I have a question. How do I modify the area where it has a static date like "20240819_1.bak" to wildcard kinda like "..2024*_1_bak'? I updated the post with the script that I have. (on the top)
• Ronaldo: @Java what kind of backup generated those files: FULL, DIFF or LOG backup? In the original script you were treating them as if they were log backups, now, from the generated script of update (8/19/2024) it seems you're restoring from a full backup. Do you still have an error?
• Java: @Ronaldo I guess they are full back up. Sorry. I am still trying to learn this stuff. Sorry that I confused you. I actually gave up on my previous T-SQL and posted to a new thread (stackoverflow.com/questions/78890056/…) using the generated T-SQL. Thank you.

Answer 1

Your problem seems to be that you're treating each file as if it was a new backup when they're actually all pieces of only one backup file. Your final command should look something like this:

RESTORE LOG [WH_BEE] FROM 
DISK = 'F:\NHFilesToday\WH_BEE_1.bak',
DISK = 'F:\NHFilesToday\WH_BEE_2.bak',
DISK = 'F:\NHFilesToday\WH_BEE_3.bak',
DISK = 'F:\NHFilesToday\WH_BEE_4.bak',
DISK = 'F:\NHFilesToday\WH_BEE_5.bak'
WITH STANDBY = 'F:\backup\WH_BEE.bak'

To achieve it, you can replace your step 5 for this:

-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR 
 SELECT backupFile 
 FROM @fileList 
 WHERE backupFile LIKE '%.bak' 
 AND backupFile LIKE @fileName + '%' 
OPEN backupFiles 
-- Loop through all the files for the database 
FETCH NEXT FROM backupFiles INTO @backupFile 
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM ' + CHAR(13) 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @cmd = @cmd + 'DISK = ''' + @backupPath + @backupFile + ''',' + CHAR(13)
 FETCH NEXT FROM backupFiles INTO @backupFile
END
SET @cmd = SUBSTRING(@cmd,1,LEN(@cmd)-2) + CHAR(13) + 'WITH STANDBY = '''+ @standby + ''''
EXEC (@cmd)
CLOSE backupFiles 
DEALLOCATE backupFiles

• Java: Thank you for your help. I replaced the Part 5 with your code and got the error on the top (Update on the original post). Does Part 6 look ok (stay same)? Also, the original DB is on "normal" status.
• Ronaldo: @Java, your new error seems to imply that you're trying to restore a backup from a database that was created on an older (or maybe not patched) version of SQL Server to a newer one. If that's the case, it might be a limitation of the option WITH STANDBY and the correction would be just remove that option of the restore command. Please, run a SELECT @@VERSION on both servers to compare if the server your're restoring has a newer version of SQL Server.
• Ronaldo: @Java see This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY