Backups: Doing it right so you can recover quickly! – Checking backups have occurred.

[Next Post]

Over the last few months I have been reviewing presentations at user conferences and talking to consultants about their experiences. Backups are a real problem for many installations. The two critical problems that I have seen are:

  • Transaction logs containing years of transactions.
  • User presentations advising an inappropriate backup model.

Either of these situations can result in recovery being impossible -- putting your career on the line. Before we go on, I would suggest you read (and do) a copy only backup of your database immediately (see this visual step-by-step guide) so you can recover today's database state at least.

How am I at risk with the above problems?

Risk with huge transaction logs

Any computer file has a risk of corruption. If a transaction log is corrupted, then everything after the point of corruption could be lost or require a massive amount of manual intervention. Consider losing a record insert that provides referential integrity (foreign key) to 20 other tables; every transaction referencing this record will fail because the key would not exist.

A second issue is the time to process the transaction log. If you have 5 years of transactions, then you will have to wait until these five years of transactions are applied to your last full backup. Transaction log backups are applied in a serial manner using just one CPU to insure that the sequence (dependency between logged transactions) is preserved. You may have a 64 core SQL Server, applying the transaction log may only use one of these cores.

I have worked in a group where a database took 17 days to restore from the transaction log because scheduling appropriate backup fell through the cracks.

Risk with wrong backup model

With SQL Server, there are multiple backup modes as shown in the table below. The term "work loss exposure" indicates changes that are lost -- and you should consider carefully what the consequence would be to your firm of losing a day, a week, a month or even a year of work.

Recovery Model


Type of Backups

Simple Recovery

All work since the last backup is lost. Transaction logs are erased (truncated) after the backup. Full

Full Recovery

All work is available until the moment of failure. Transaction logs may be used to bring the database up to the moment of failure Full
Transaction Log

Bulk-Logged Recovery

This is like the full recovery model except certain activities are not recorded. If any of these skipped activities occur, then you cannot use the transaction log in a restore and there will be a work loss exposure. Full
Transaction Log

If the database uses any TEXT, NTEXT, IMAGE columns, then bulk-logged recovery should not be used because updates will not be logged.

What is the state of your backups?

The TSQL below will create a stored procedure that will summarize your databases and their backup state for all databases on a SQL Server Instance.

CREATE proc SQLISV_BackupStatusSummary AS DECLARE @Cmd nvarchar(max) IF NOT EXISTS(SELECT name from sysobjects where name='SQLISV_LogSpace' and type='P') BEGIN SET @CMD='CREATE Proc SQLISV_LogSpace AS DBCC SQLPERF(logspace)' EXEC (@Cmd) END CREATE TABLE #LogSpace ( DBName sysname, logSize decimal(18,5), logUsed decimal(18,5), status int ) INSERT INTO #LogSpace exec SQLISV_LogSpace SELECT, Recovery_Model, case t1.Type when 'D' then 'Full' when 'L' then 'Transaction Log' when 'I' then 'Differential' Else '-- missing --' end as [BackupType], Max(cast(T1.BACKUP_SIZE/1048576 as decimal(18,2))) AS [BackupSizeMB], Max(DateDiff(ms,backup_start_date,backup_finish_date)) as BackupMsec, Max(Cast(logSize* logUsed/100 as decimal(18,2))) as TransactionLogSizeMB, Max(T1.backup_finish_date) as [BackupCompleted] FROM sys.sysdatabases T2 LEFT JOIN msdb.dbo.backupset T1 ON T1.database_name = AND is_copy_only = 0 LEFT JOIN #LogSpace ON WHERE T2.Name not in ('tempdb','master','model','msdb') Group by T2.Name, T1.Type,Recovery_Model GO

Executing it will produce a summary similar to that shown below.

exec SQLISV_BackupStatusSummary



Note: The TransactionLogSizeMB is the current size and not the size when a backup occurred.

Next up: Checking that the database and the backups on not on the same drive.

Comments (1)

  1. Minaz Amin says:

    Simple and smart script.

Skip to main content