SA0088 : The last full backup for the database cannot be found on the location where it was initially created
Introduction
Section titled “Introduction”Ensure the backup history in SQL Server accurately reflects the actual locations of data and backup files to prevent restore operation issues.
Description
Section titled “Description”This problem occurs when the backup history in SQL Server does not accurately reflect the actual locations of both data files and backup files. Such discrepancies can lead to significant issues during restore operations.
For example:
-- Example of querying backup locationSELECT backup_start_date, physical_device_nameFROM msdb.dbo.backupmediafamily;If the physical_device_name in the backup history does not match the current file locations, it may result in errors during database recovery processes.
-
Data recovery failures due to invalid file paths may occur.
-
Mistrust of backup reliability, leading to uncertainty in data integrity.
How to fix
Section titled “How to fix”This guide provides steps to resolve inconsistencies in the SQL Server backup history, ensuring backup files are correctly reflected in their original locations.
Follow these steps to address the issue:
1.Query current backup locations by running the following SQL command:
2.Verify the physical_device_name against the actual location of the backup files on the file system. Use file management tools to locate these files.
3.If discrepancies are found, update the records or move the backup files to their expected locations. Ensure the physical_device_name matches the true file path for each backup record.
4.Regularly check and maintain backup history consistency by setting up monitoring scripts or automated verification processes.
For example, if a mismatching path is detected, move the file to the correct location:
-- Correct physical device path-- MOVE command example (to be run outside SQL Server, such as a command shell)MOVE "C:\Backups\CorrectLocation\BackupFile.bak" "C:\CurrentLocation\BackupFile.bak";The rule has a ContextOnly scope and is applied only on current server and database schema.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Maintenance Rules, Bugs
Additional Information
Section titled “Additional Information”How to check that backup files exist