SA0088 : The last full backup for the database cannot be found on the location where it was initially created

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:

1-- Example of querying backup location
2SELECT backup_start_date, physical_device_name
3FROM 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.

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:

1-- Correct physical device path
2-- MOVE command example (to be run outside SQL Server, such as a command shell)
3MOVE "C:BackupsCorrectLocationBackupFile.bak" "C:CurrentLocationBackupFile.bak";

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Maintenance Rules, Bugs

How to check that backup files exist

Other Resources