0

I moved some backup files to new destination. However, the SQL Server seem to think they are still in their old place. (That is, when I use the SSMS Restore Database wizard, it it still comes up with restore plan using the old location.)

Is there way to move the files so that the SQL Server gets notified about it?

1 Answer 1

1

There's a table called dbo.backupmediafamily in database msdb. It contains physical_device_name column, which can be correctly updated.

Using the script below you can get list of last 100 registered backups of database DB:

SELECT TOP 100 bs.server_name
    ,bs.database_name
    ,bs.recovery_model
    ,convert(NUMERIC(10, 2), bs.backup_size / 1024 / 1024) [backup, Mb]
    ,bs.compressed_backup_size
    ,bs.backup_start_date
    ,bs.backup_finish_date
    ,datediff(ss, bs.backup_start_date, bs.backup_finish_date) [Duration (sec)]
    ,bs.name
    ,bs.user_name
    ,CASE bs.type
        WHEN 'D'
            THEN 'Database full'
        WHEN 'I'
            THEN 'Database Differential'
        WHEN 'L'
            THEN 'Log'
        WHEN 'F'
            THEN 'File or Filegroup'
        WHEN 'G'
            THEN 'File Differential'
        WHEN 'P'
            THEN 'Partial'
        WHEN 'Q'
            THEN 'Partial Differential'
        ELSE 'Unknown'
        END 'backup type' -- from BOL    
    ,bmf.media_set_id
    ,bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'DB'
ORDER BY backup_finish_date DESC
3
  • Directly updating system tables sounds... dangerous? Commented May 22 at 9:57
  • Yes, it sounds so, but if you know what you are doing, it's not in this case. It's like updating Windows registry. Commented May 22 at 10:11
  • 2
    @Yano_of_Queenscastle In the end it is up to you. Either go unsupported and modify the system tables. Or just accept that SQL has the wrong idea of where those backup files are and because of that not be able to do restore in the GUI based on backup history. Commented May 22 at 11:17

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.