Telling SQL Server about moving the backup files

Question

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?

Answer 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