Background
We are in the midst of a Configuration Manager Server upgrade to new servers running Windows Server 2019 and SQL Server 2019. As part of migration we are also moving our SQL Database off of our CAS to its own dedicated DB server.
After moving the database, we found our SMS Site Backup task was then failing with a SQL Writer related error.
SMSBKUP.LOG ERRORS
Checking SMSBKUP.LOG I found the following errors.
ERROR: SQL Backup task failed. Error message - Error: SQL Writer not found. STATMSG: ID=5052 SEV=E LEV=M SOURCE="SMS Server" COMP="SMS_SITE_BACKUP" SYS=. SITE=<SiteCode> PID=11948 TID=12044 GMTDATE=Thu Oct 07 07:07:26.666 2021 ISTR0="\" ISTR1="CM_;" ISTR2="Error: SQL Writer not found." ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 LE=0X0 StateTable::CState::Handle - (5052:3 2021-10-07 07:07:26.666+00:00) >> (5035:1 2021-10-06 07:13:03.749+00:00) Inbox source is local on CStateMsgReporter::DeliverMessages - Queued message: TT=1401 TIDT=0 TID='EF736CF9-7214-4796-9C85-2549A9056D8D' SID=5052 MUF=0 PCNT=2, P1='' P2='2021-10-07 07:07:26.666+00:00' P3='' P4='' P5='' CStateMsgReporter::DeliverMessages - Created state message file: <ConfigMgrFolder>\inboxes\auth\statesys.box\incoming\c74e57dh.SMX Successfully send state change notification EF736CF9-7214-4796-9C85-2549A9056D8D Error: Sql Server could not prepare for the Backup. Aborting the backup process. Aborting the SQL backup process. Current state of SQL backup: [failed]. Aborting it. SMS_SITE_BACKUP failed. Please see previous errors. STATMSG: ID=5060 SEV=E LEV=M SOURCE="SMS Server" COMP="SMS_SITE_BACKUP" SYS=. SITE= PID=11948 TID=12044 GMTDATE=Thu Oct 07 07:07:27.166 2021 ISTR0="Error: Sql Server could not prepare for the Backup." ISTR1="" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 LE=0X0
Checking on the new SQL Server had a look at the VSS Writers.
vssadmin list writers
Looking through the list of writers, all were stable but the SqlServerWriter was MISSING!
After some searching I found a post about NT AUTHORITY\SYSTEM needing sysadmin rights to SQL Server. Checking permissions on my server the SQL Server VSS Writer was running as SYSTEM but NT AUTHORITY\SYSTEM only had Public rights within SQL Server. I added sysadmin rights and re-ran the vssadmin list writers command.
This time, SqlServerWriter shows up!
I re-ran my SQL Backup and the SQL Writer error was gone but I ran into another error.
Error: SQL Backup failed for component CM_<SiteCode> ERROR: Failed to backup SQL component CM_<SiteCode>. Error = ERROR: CTool::CreateDir was unable to create backup destination path \\<ServerName>\CMBackup$\Backup\SiteDBServer Error: Backup Failed for Component - <SiteCode>Backup\SiteDBServer\SMSbkSQLSiteDB.dat.
As you can see, it failed to create the destination path SiteDBServer folder in the target backup location.
Checking Share and Security permissions on our CMBackup$ share I found we were missing the computer account for the new SQL Server (duh!) so I added it with Full Control to the share and Security level permissions.
Note: If you have a SQL Cluster you should add permissions for both nodes of the SQL cluster.
Once this was done I ran another ConfigMgr Backup and this time the backup completed successfully.
Info: Starting asynchronous BackupComplete…
Info: Asynchronous BackupComplete finished.
After BackupComplete SMS Writer status = STABLE.
Backup completed - Thu Oct 07 07:53:35 2021
Hope this helps!