Home Forums SQL Server 2005 Administering I just became Keeper of the Ring. And I'm Boromir. RE: I just became Keeper of the Ring. And I'm Boromir.

  • Here is a quick script to see the last backup for each database on the server. Gives file name (so you know where they are going), whether the database is a current database (could have been dropped in the past), and the recovery model.

    Should give you an idea of the basics.

    /* ========================================================================================================================== */

    /* Get the very last backup set (Database and all Tranlog backups) for each database in instance */

    /* ========================================================================================================================== */

    CREATE TABLE #dummybackups

    (

    DBNamevarchar(300),

    LastBackupDatedatetime

    )

    CREATE TABLE #dummydb

    (

    DBNvarchar(300),

    RecModvarchar(50)

    )

    INSERT INTO #dummydb

    SELECT name, recovery_model_desc FROM master.sys.databases

    WHERE name NOT IN ('tempdb') AND is_read_only = 0 AND state = 0

    INSERT INTO #dummybackups

    SELECT DISTINCT database_name, NULL FROM msdb..backupset ORDER BY database_name

    --Get last full backup for each database and put in dummy table for that database

    UPDATE #dummybackups

    SET LastBackupDate = (SELECT TOP 1 backup_start_date FROM msdb..backupset WHERE database_name = DBName AND type = 'D' ORDER BY backup_start_date DESC)

    SELECT S.database_name,

    CONVERT(varchar(20),'') AS CurrentDB,

    S.recovery_model, S.user_name,

    S.backup_start_date,

    CASE WHEN CONVERT(varchar(200),GETDATE(),101) = CONVERT(varchar(200),S.backup_start_date,101) THEN '' ELSE ' * ' END AS NotFromToday,

    S.type,

    CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) AS backup_size_MB, M.physical_device_name

    INTO #dummybackups2

    FROM msdb..backupset S

    JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id

    WHERE S.backup_start_date >= (SELECT LastBackupDate FROM #dummybackups WHERE database_name = DBName) AND (S.recovery_model IS NOT NULL)

    --AND S.database_name = 'somedbname'

    ORDER by S.database_name, backup_start_date DESC

    UPDATE #dummybackups2

    SET CurrentDB = 'NO'

    WHERE NOT EXISTS(SELECT name FROM master.sys.databases WHERE master.sys.databases.name = database_name)

    -- insert into final resultset the databases that are in the instance, but don't have a record in the backups

    INSERT INTO #dummybackups2

    SELECT DBN,'',RecMod,'',NULL,'','',0,NULL FROM #dummydb WHERE DBN NOT IN (SELECT database_name FROM #dummybackups2)

    SELECT *

    ,ISNULL(SUBSTRING(physical_device_name,1,LEN(physical_device_name) - PATINDEX('%\%',REVERSE(physical_device_name)) + 1),NULL) AS PathName

    ,ISNULL(SUBSTRING(physical_device_name,LEN(physical_device_name) - PATINDEX('%\%',REVERSE(physical_device_name)) + 2,PATINDEX('%\%',REVERSE(physical_device_name))-1),NULL) AS FileNm

    FROM #dummybackups2 ORDER BY database_name, backup_start_date DESC

    DROP TABLE #dummybackups

    DROP TABLE #dummybackups2

    DROP TABLE #dummydb