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 |
Description |
Type of Backups |
|
|
All work since the last backup is lost . Transaction logs are erased (truncated) after the backup. |
Full
Differential |
|
|
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
Differential Transaction Log |
|
|
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
Differential 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.
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.
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.