0

This morning I have perform a differential backup of my database, named "MW". Now I need to restore the backup (full+differential) on another database, named "bak_MW".

I got an error trying to restore the database with full backup + diff backup.

The error is:

Restore of database 'bak_MW' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------ ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'bak_MW' database. (Microsoft.SqlServer.SmoExtended)

I think the error is related to the wrong source file of full backup. Some process has made a full backup newer than mine.

How can I get the filename refenced by MSSQL Backup for full backup?

Is there some way to find the log of full backup performed, with destination file path?

Thanks.

2 Answers 2

2

Your differential backup might be useless if the last full backup was done via VSS.

SELECT TOP (1) M.device_type, S.backup_start_date, S.backup_finish_date, M.physical_device_name, S.is_copy_only
FROM msdb.dbo.backupset S
    JOIN msdb.dbo.backupmediafamily M
        ON S.media_set_id = M.media_set_id
WHERE S.[type] IN ('D') -- Full Backup (I = Diff, L = Log)
    AND S.[database_name] = '<Database_Name, sysname, Database_Name>'
    -- 7 is the snapshot done by tape backup
    -- AND M.device_type = 2
ORDER BY S.backup_finish_date DESC;
2
  • Sadly yes, it is 7. Is there a way to make a differential backup starting from another full backup?
    – Radioleao
    Commented Mar 21 at 17:22
  • No. You need to make sure your infrastructure people do their VSS backup with is_copy_only = 1 so the differential LSN is not reset. If your db is in FULL recovery, and you still have the log backups, you should be able to do a point in time restore from a previous full backup and the subsequent log backups.
    – Aardvark
    Commented Mar 22 at 13:40
0

Some process has made a full backup newer than mine.

I don't think so. If your diff based on different full the error message should be like 'This differential backup cannot be restored because the database has not been restored to the correct earlier state.'

How can I get the filename refenced by MSSQL Backup for full backup? Is there some way to find the log of full backup performed, with destination file path?

There are two places where you can get data

  1. MSDB database in source
  2. Backup file itself

In the first case you should run query similar to the one Aardvark provided against

msdb.dbo.backupset

https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-ver16

msdb.dbo.backupmediafamily

https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql?view=sql-server-ver16

Another point of view is to look at your backup files and run restore command

  • RESTORE FILELISTONLY to get list of backups in the backup file in case there is more than one

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-filelistonly-transact-sql?view=sql-server-ver16

  • RESTORE HEADERONLY to get contents of backup

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql?view=sql-server-ver16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.