When was your last backup run?

SQL Server db backup history - Part 4

When you do a database restore, including log backups, it is very important to get the log chain in the right order, or it won’t work.  To restore a full backup, then log backups, the chain of logs could be hard to sort out if you have dozens or hundreds of log backups to restore.  Your sure don’t want to be hand coding these.
The script below shows how to find the last full backup, then get a list of all of the log backups that have occurred since that last full backup.  To do the restore, you should just follow the order of the output of this script.
Sample Code


DECLARE @dbName VARCHAR(1024);
-- be sure to put your database name on the following line.
SET @dbName = 'YourDatabaseName';

DECLARE @bfd DATETIME;
DECLARE @fullbackup_file VARCHAR(2048);

SELECT TOP 1
@fullbackup_file = m.physical_device_name,
@bfd = b.backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = @dbName
AND TYPE = ‘D’ 

-- type D = full database backup
ORDER BY backup_set_id DESC;
SELECT @fullbackup_file AS backup_file_name,
@bfd AS backup_finish_date
UNION
SELECT m.physical_device_name AS backup_file_name,
b.backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = @dbName
AND backup_finish_date > @bfd
AND TYPE = ‘L’ 

-- type L = log backup
ORDER BY backup_finish_date ASC;

The output will look something like this…

Once you have the list of files that need to be restored you can then just format it into a sql script to do the full restore.

Keep in mind that if your database has just failed, you are not going to have access to this information, so I suggest that you script this and save it out as a sql script in the same directory as your backup files so that it can be used for the restore.  This process will be covered in a later posting.

When was your last backup run?

One of the first things I query when I take on a contract -- what type of backups are being run, and when?  On one of my recent contracts, no names mentioned, I found that no backups were being run at all!!  Run this little ditty on a single instance, or across multiple instances through your CMS.  It returns a helpful summary on the status of your backups.

  SET NOCOUNT ON;

  DECLARE @Results TABLE (
    ServerName VARCHAR(100) NULL,
    DatabaseName VARCHAR(100) NULL,
    RecoveryModel VARCHAR(100) NULL,
    LastFullBkupTime DATETIME NULL,
    DaysSinceLastFull INT NULL,
    DaysSinceLastDiff INT NULL,
    HoursSinceLastLogBkup INT,
    DBStatus VARCHAR (100) NULL,
    BkupFile VARCHAR(1000) NULL,
    Media INT
  )

  /* Get Server and database names. */

  INSERT @Results(ServerName,DatabaseName)
  SELECT CONVERT(VARCHAR,SERVERPROPERTY('ServerName')),a.name 
  FROM master..sysdatabases a
  WHERE a.name <> 'tempdb'

  /* Last full bkup time and media. */

  UPDATE @Results
  SET 
      LastFullBkupTime = b.backup_start_date,
      DaysSinceLastFull = DATEDIFF(dd,b.backup_start_date,GETDATE()),
      Media = b.media_SET_id
  FROM @Results a,(
    SELECT database_name, MAX(media_SET_id)media_SET_id, MAX(backup_start_date) backup_start_date 
    FROM msdb..backupset 
    WHERE TYPE = 'D' 
    GROUP BY database_name) b
  WHERE 
    a.DatabaseName = b.database_name

  /* Database status */

  UPDATE @Results 
  SET DBStatus = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Status'))

 

  /* Recovery model */

  UPDATE @Results 
  SET RecoveryModel = CONVERT(sysname,DatabasePropertyEx(DatabaseName,'Recovery'))

  /* Backup file location. */

  UPDATE d
  SET d.BkupFile = b.physical_device_name
  FROM @Results d, msdb..backupmediafamily b
  WHERE d.Media = b.media_SET_id
 
  /* Days since last diff bkup. */
  UPDATE d 
  SET d.DaysSinceLastDiff = DATEDIFF(dd,b.backup_finish_date,GETDATE())
  FROM @Results d, (
     SELECT database_name,MAX(backup_finish_date) backup_finish_date 
     FROM msdb..backupset 
     WHERE TYPE  = 'I'  
     GROUP BY database_name) b
  WHERE d.DatabaseName = b.database_name

  /* Hours since last log dump. */

  UPDATE d 
  SET d.HoursSinceLastLogBkup = DATEDIFF(hh,b.backup_finish_date,GETDATE())
  FROM @Results d, (
     SELECT database_name,MAX(backup_finish_date) backup_finish_date 
     FROM msdb..backupset 
     WHERE TYPE ='L'  
     GROUP BY database_name) b
  WHERE d.DatabaseName = b.database_name
  AND d.RecoveryModel <> 'SIMPLE'

  /* Bring it back for review. */

SELECT
     ServerName,
     DatabaseName,
     RecoveryModel,
     LastFullBkupTime,
     DaysSinceLastFull,
     DaysSinceLastDiff,
     HoursSinceLastLogBkup,  
     DBStatus,
     BkupFile,
     Media
 FROM
     @Results

  SET NOCOUNT OFF;