Home Forums SQL Server 2008 T-SQL (SS2K8) Need a backup script to get backup history status for the latest backup from all linked sql servers RE: Need a backup script to get backup history status for the latest backup from all linked sql servers

  • If you use T-SQL, you'll probably want to use a cursor to loop through all of the linked servers. You can find all the linked servers in the Sys.Servers system view. You'll probably want to filter by the provider column to limit it to links to other SQL Server machines.

    Then for each link, you'll want to get the backup information you are interested in. You can find backup information in the following tables:

    msdb.dbo.backupset

    msdb.dbo.backupmediafamily

    Master.sys.databases

    I believe they join together like this:

    <linked server name>.msdb.dbo.backupset Backup_Set

    Join

    <linked server name>.msdb.dbo.backupmediafamily Media

    ON

    Backup_Set.Media_set_id = Media.media_set_id

    AND Backup_Set.is_copy_only = 0

    Left Join

    <linked server name>.Master.sys.databases DB

    ON

    Backup_Set.database_name = DB.name

    The whole backup history is in there, so you'll have to determine which backups you want and what backup information you are looking for and filter appropriately.

    Once you have all the pieces, you use your cursor to loop through the linked servers, use dynamic SQL to run your query for each linked server, collect all the results in a temp table or table variable, and then select those results back out formatted as HTML...

    http://gallery.technet.microsoft.com/scriptcenter/Selection-result-as-HTML-a1ccff98

    ...and send them off in an email with the HTML results variable as the body.

    I hope this helps. Good luck!