table name that contains the list of files that were restored in a database

  • hi,

    is there a list of table that contains the name of all the files that has been restored in a database?  i checked the system tables restorefile, restorefilegroup and restorehistory from msdb and they dont have the actual filenames of the backup that were restored.  sort of like the backupmediafamily system table for the list of backup that were created.

    thanks.

    ann

     

  • are you looking for something like this:

    select logical_name, physical_name, filegroup_name, user_name, destination_database_name, restore_date

    from restorehistory rh

    inner join

    backupfile bf

    on

    rh.backup_set_id = bf.backup_set_id

    order by restore_date desc







    **ASCII stupid question, get a stupid ANSI !!!**

  • close.  can i actually get the actual name of the backup file that has been applied?  for example, if apply a backup with name servce_20050714_0600_log.bak where will i get that name?

    when you restore a database, the screen shows you the names of the files that has been applied to that database. where does sql gets that info?

  • hmmm - ann - I see the name only in the "backupmediafamily" - still looking to see how it links up with the restore tables....







    **ASCII stupid question, get a stupid ANSI !!!**

  • Folks,

    This query when executed on the master table will list all the full databse backups in the last 25 hours... It can of course be manipulated to log backups[ type='L'

    ] and a different time[dateadd(hh,-25,getdate())] ...

    select database_name,

           physical_device_name      

    from   msdb.dbo.backupset as bkupset,

        msdb.dbo.backupmediafamily as bkupfamily

    where  type='D'

    and    backup_start_date >

           (dateadd(hh,-25,getdate()))

    and    bkupfamily.media_set_id =

        bkupset.media_set_id

    Regards,

    M

     

     

  • maertean - thanks - I beilieve though (ann can set me straight) - that she needs a list of all the files that have been restored - so do you know how to link the backup files with the restorefiles ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • ann - could you pl. check out this query and see if it works...I can't test this right now...

    select restore_type, physical_device_name, destination_database_name, restore_date

    from restorehistory rh

    inner join

    backupset bs

    on

    rh.backup_set_id = bs.backup_set_id

    inner join

    backupmediafamily bf

    on

    bs.media_set_id = bf.media_set_id

    and restore_type = 'D'

    order by restore_date desc







    **ASCII stupid question, get a stupid ANSI !!!**

  • hi sushila,

    yup, ur right in the sense that i need the list of RESTORED files.  ur sql statement looks about just right and i ran it from the analyzer and it returned the information that i need.

    i just replaced the D with L coz i need to track all the restore of tran logs.

    thanks heaps =)

    ann

  • oh good - thx for getting back on this one 'cos I still haven't been able to test it!







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply