SQL Server Performance

where sql write all the backup details

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Dec 16, 2007.

  1. avipenina New Member

    Hi,
    where sql server 2005 writes all the details of the backup that he do?
    what i mean is if i did a tlog backup, on what table it's stored the backup details like - what database was backup, what was the location,what file name,what time, and other stuff like.
    Thx
  2. ranjitjain New Member

    Hi,
    Backup and restore history is stored in MSDB system database in various system tables. Few of such tables are: backupset, backupfile etc...
  3. avipenina New Member

  4. satya Moderator

    Overview of Backup and Restore History Tables
    This section introduces the history tables that store backup and restore metadata in the msdb system database.
    History table Description
    /msdn.microsoft.com/mshelp" />backupfile Contains one row for each data or log file that is backed up.backupfilegroupContains a row for each filegroup in a backup set. This table is new in SQL Server 2005.
    backupmediafamilyContains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set. Media sets that exist at the time of upgrade to SQL Server 2005 have a single row per media family.
    backupmediasetContains one row for each backup media set.
    backupsetContains a row for each backup set.
    restorefileContains one row for each restored file. This includes files restored indirectly by filegroup name.
    restorefilegroupContains one row for each restored filegroup.
    restorehistoryContains one row for each restore operation
  5. avipenina New Member

    Thx.
    I'm trying to write a tsql that will bring me form all the backup tables ,the physical name of the backup file, the start and end ,the location of the backup,and more results.
    how i do that?
    i try this tsql but i can't fit the table that holds the location of the backup
    select t1.backup_set_id,t1.backup_start_date,t1.backup_finish_date,t2.logical_name,t2.backup_size
    from backupset t1,backupfile t2
    where t1.backup_set_id=t2.backup_set_id
    order by t1.backup_start_date desc
    Plz help to complete this tsql.
    Thx
  6. satya Moderator

    Why not take help of SP_HELP_JOB_xxx related stored procedures that will have relevant information, just import results to a temp.table and you query that table, rather than writing and accessing system tables.

Share This Page