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:
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.
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.
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.
Type of Backups
If the database uses any TEXT, NTEXT, IMAGE columns, then bulk-logged recovery should not be used because updates will not be logged.
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
DECLARE @Cmd nvarchar(max)
IF NOT EXISTS(SELECT name from sysobjects where name='SQLISV_LogSpace' and type='P')
SET @CMD='CREATE Proc SQLISV_LogSpace AS DBCC SQLPERF(logspace)'
CREATE TABLE #LogSpace
INSERT INTO #LogSpace exec SQLISV_LogSpace
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
WHERE T2.Name not in ('tempdb','master','model','msdb')
Group by T2.Name, T1.Type,Recovery_Model
Executing it will produce a summary similar to that shown below.
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.
Simple and smart script.