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.

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 
    T2.name,
    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 = T2.name
AND is_copy_only = 0
LEFT JOIN #LogSpace
ON DBName=T2.name
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.