SQL Backup fails with SQL3
Problem description
SQL Backup fails with SQL3
Cause
This error can occur if the metadata of the database selected for backup does not match with the database metadata when it was backed up earlier.
Traceback
Line 180: [2022-09-20 02:45:50,473] [ERROR] <_MainThread(MainThread)> Instance/DB entry : [USW199/SelfService_Na_Prod] missing in full_bkp_st_time_map ={u'USW199': {u'USintranetconnections': u'2022-09-20 02:39:31', u'Morphee64': u'2022-09-20 02:39:31', u'EPLAN_Parts_DUMP': u'2022-09-20 02:39:31', u'SelfService_Na_Prod ': u'2022-09-20 02:39:32', u'Wasp': u'2022-09-20 02:39:31', u'Morphee': u'2022-09-20 02:39:31', u'tc_test': u'2022-09-20 02:39:31', u'LabLink': u'2022-09-20 02:39:31', u'fev_inc_benchmarking_Test': u'2022-09-20 02:39:32', u'fev_inc_benchmarking': u'2022-09-20 02:39:32', u'EPLAN_Projects': u'2022-09-20 02:39:32', u'master': u'2022-09-20 02:39:32', u'SQBoxStatistics': u'2022-09-20 02:39:32', u'msdb': u'2022-09-20 02:39:32', u'EPLAN_Translate': u'2022-09-20 02:39:32', u'model': u'2022-09-20 02:39:32', u'EPLAN_Parts': u'2022-09-20 02:39:32', u'tc_prod': u'2022-09-20 02:39:32', u'ePO_USW0167': u'2022-09-20 02:39:32'}}
Line 181: [2022-09-20 02:45:50,473] [ERROR] <_MainThread(MainThread)> SyncError: Metadata mismatched from last backup. SQL Server instance was probably not backed up in previous backup cycle.
Resolution
- In msdb.dbo.backupset, the table has old backup entries in which "database_name" column has "SelfService_Na_Prod " (database name with space)
- Please run the following SQL query on the production server and initiate the full backup.
USE master;
UPDATE msdb.dbo.backupset SET database_name = 'new_database_name' WHERE database_name = 'old_database_name'; - Example:
use master;
update msdb.dbo.backupset set database_name = 'SelfService_Na_Prod' where database_name = 'SelfService_Na_Prod ';
NOTE: The SQL statement provided is for updating the database name in the backupset table of the msdb database. It is changing the value of the database_name column from 'SelfService_Na_Prod ' to 'SelfService_Na_Prod'.
Here's a breakdown of the statement:
- use master;: Switches to the master database
- update [msdb].[dbo].[backupset]: Specifies the table to update, which is [backupset] in the [dbo] schema of the msdb database.
- set database_name = 'SelfService_Na_Prod': Sets the database_name column to the new value 'SelfService_Na_Prod'
- where database_name = 'SelfService_Na_Prod ';: Filters the rows to update based on the current value of the database_name column, which is 'SelfService_Na_Prod '.
To see the existing backup sets entries run the following sql query:
select '#' + database_name + '#' from [msdb].[dbo].[backupset] where type='D' and is_copy_only=0
Here's a sample output for the given command:
(No column name)
#AdventureWorks2019#
#AdventureWorksDW2019#
#TestDatabase#
- The output shows the results of the query which selects the database name from the [backupset] table in the [msdb] database and concatenates it with # symbol at the beginning and end. The query filters the results to only include full database backups (type='D') that are not copy-only (is_copy_only=0).
- The database_name parameter is a column in the [backupset] table that stores the name of the backed up database. The type parameter is also a column in the same table that stores the type of backup, which can be full ('D'), differential ('I'), or transaction log ('L'). The is_copy_only parameter is a column that indicates whether the backup was taken as a copy-only backup (1) or not (0).
Please consult SQL Administrator before running the above query.