SQL错过了完整的备份脚本

Question

编辑：我已经带回了我需要的信息，但是其中的子句并未过滤''l'的所有备份，如果我从周围取括号（bak）。有什么想法吗？

这就是我所拥有的。我试图加入msdb..backupset和msdb.dbo.dbo.backupmediafamily，以将type作为d和device_type作为2。任何帮助都将被应用。谢谢！

SELECT DISTINCT
    CONVERT(varchar,GETDATE()-1,111) AS Missing_Backup_Date,
    @@servername AS Instance_Name,
    bak.database_name AS Database_Name,
    DATENAME(weekday, getdate()-1) AS Backup_Day_of_Week,
    bak.type AS Failure_Count,
    fam.device_type,
        CASE fam.device_type
            when 2 then 'SQL'
            when 7 then 'Avamar'
        END AS Backup_Type
FROM msdb..backupset bak 
    JOIN msdb.dbo.backupmediafamily fam 
        ON bak.media_set_id = fam.media_set_id
WHERE bak.database_name NOT IN ('tempdb','msdb','master','model')
    AND (bak.type = 'D'
    AND bak.backup_finish_date IS NULL OR bak.backup_finish_date < DATEADD(hour, -24, GETDATE()))

Answer 1

这是我用来找出备份链的脚本。SQL注入没有保护，因为它是一个管理脚本。即 - 限制访问。

只需添加其他字段和过滤。

/******************************************************
 *
 * Name:         usp_get_backup_chain.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-21-2014
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  What is the backup chain for a database?
 *
 ******************************************************/
/* 
    Choose the database.
*/
USE [msdb]
GO

/*  
    Drop the old stored procedure.
*/
IF OBJECT_ID('[dbo].[usp_get_backup_chain]') > 0
DROP PROCEDURE [dbo].[usp_get_backup_chain]
GO

/*  
    Create the new stored procedure.
*/
CREATE PROCEDURE [dbo].[usp_get_backup_chain]
    @NAME SYSNAME = '*'
AS
BEGIN
    -- Declare variables
    DECLARE @VAR_TSQL VARCHAR(2048);
    -- Dynamic T-SQL
    SET @VAR_TSQL =
     'SELECT
          s.server_name,
          s.database_name,
          s.name as software_name,
          CASE s.[type]
              WHEN ''D'' THEN ''Database''
              WHEN ''I'' THEN ''Differential database''
              WHEN ''L'' THEN ''Log''
              WHEN ''F'' THEN ''File or filegroup''
              WHEN ''G'' THEN ''Differential file''
              WHEN ''P'' THEN ''Partial''
              WHEN ''Q'' THEN ''Differential partial''
              ELSE ''none''
          END AS backup_type,
          s.backup_start_date,
          s.backup_finish_date,
          f.physical_device_name
      FROM 
          msdb.dbo.backupset AS s 
      JOIN
          msdb.dbo.backupmediafamily as f 
      ON  
          s.media_set_id = f.media_set_id
          ';
    -- All databases?
    IF @NAME <> '*'
        SET @VAR_TSQL = @VAR_TSQL + ' WHERE s.database_name = ' + CHAR(39) + @NAME + CHAR(39);
    -- Show in desc order
    SET @VAR_TSQL = @VAR_TSQL + 'ORDER BY s.backup_start_date DESC ';
    -- Run the command
    EXECUTE(@VAR_TSQL);
END;
GO

如果您真的对设备类型感兴趣，请在动态SQL的末尾添加信息。

这是每个bol -http：//technet.microsoft.com/en-us/library/ms190284.aspx。

      CASE f.device_type
          WHEN 2 THEN 'Disk'
          WHEN 5 THEN 'Tape'
          WHEN 7 THEN 'Virtual device'
          WHEN 105 THEN 'Permanent device'
          ELSE ''
      END AS device_types

再次，如果您不传递数据库名称，则此列表。

您还想要什么？