I am working on migration of SQL Server from SQL 2016 to SQL 2022.
I have a process where after database is restored (to "Standby / Read-Only" state) from Full tape (from a third vendor), I am supposed to add each hourly log file (bak) into this "Standby / Read-Only" database.
I was trying to run the T-SQL code that was working in SQL 2016 (for last 4 years) to this new environment (SQL 2022), and first error message that I got was on this part:
EXEC master.sys.xp_cmdshell @cmd
So, I Googled it, and ran this code:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
When I ran the code, there seems to be no error message, but I do not see log file of database increases the size.
I am putting my whole T-SQL code here for reference:
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 = 'us_xxxx_multi_replica1'
SET @fileName = 'LOG_us_xxxx_multi_replica'
SET @backupPath = 'F:\Yesterday'
SET @standby = 'F:\us_xxxx_multi_replica_RollbackUndo_2024-09-17_19-33-23.bak'
-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 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
Bottom is result example after processing in SQL 2022 (that I am having an issue):
Processed 0 pages for database 'us_xxxx_multi_replica', file 'us_template_pccmulti_rrdb_replica' on file 1.
Processed 952 pages for database 'us_xxx_multi_replica', file 'us_template_pccmulti_rrdb_replica_log' on file 1.
System objects could not be updated in database 'us_xxxx_multi_replica' because it is read-only.
System objects could not be updated in database 'us_xxxx_multi_replica' because it is read-only.
RESTORE LOG successfully processed 952 pages in 1.845 seconds (4.029 MB/sec).
Bottom is result example after I ran in SQL 2016 (where there is no issue):
Processed 2336 pages for database 'us_xxx_multi_replica', file 'us_template_pccmulti_rrdb_replica_log' on file 1.
System objects could not be updated in database 'us_xxx_multi_replica' because it is read-only.
System objects could not be updated in database 'us_xxx_multi_replica' because it is read-only.
RESTORE LOG successfully processed 2336 pages in 0.531 seconds (34.361 MB/sec).
Processed 0 pages for database 'us_xxx_multi_replica', file 'us_template_pccmulti_rrdb_replica' on file 1.
What might be the issue?
BTW, when SQL 2022 was installed on this VM, there was SQL 2019 already installed (came with the VM). Our IT installed SQL 2022 manually, so I am wondering if there is some type of permission /configuration issue that was not set up correctly.
Because when I ran this T-SQL code in SQL 2019, I did not encountered the issue of
EXEC master.sys.xp_cmdshell @cmd
from the beginning.
Update:
I did not find the reason why I had to take care of the issue with 'xp_cmdshell' issue in SQL 2022 (but not in SQL 2019), but it is working fine.
I realized that ldf file in SQL 2016 did not show increase in size, but only in increase in mdf file. So, I guess I do not have to worry about no change in ldf file size.
I was able to see the differences by checking the data itself.
