SQL 2008 R2 and Idera Idera SQL Safe (Freeware Edition)

Good news: the Freeware edition of Idera SQL Safe works on R2. You might not care but I certainly do. Here’s why:

In September last year I started using Idera SQL Safe (the Freeware Edition) to get backup compression on my SQL 2005 servers. It seemed like a good idea at the time – it was free and my backups ran much faster and took up much less disk space. I really thought I’d actually scored a free lunch. Until they discontinued the product. I was thinking about what to do when I heard that R2 Standard would include native backup compression so I’ve just been keeping my fingers crossed since then. So I installed R2 Developer on my laptop, installed SQL Safe and kicked off a restore with it. No problem. Phew! Now I won’t have to do a special, non-compressed backup and restore when we migrate.

Is this log backup file the next one to restore?

When you restore from a transaction log backup, SQL Server checks the file against the database and gives you an error if the file is not the next one in the sequence. I wanted to know how this works so I did some digging. Not much in Books Online that I could see so I did some practical investigation with some of my log shipping files.

It looks like it compares the last LSN in the backup file’s header with the last LSN in the database’s restore history. If the last LSN in the file is less than the last LSN in the restore history, it indicates that the database already contains those transactions. Here’s some code that does it:

declare    @LastDBLSN    numeric(25,0)
        , @LastFileLSN    numeric(25,0)
 
--Get last LSN from database
select    top 1 @LastDBLSN = bs.last_lsn
from    msdb.dbo.restorehistory rh with (nolock)
        left join msdb.dbo.backupset bs with (nolock) on bs.backup_set_id = rh.backup_set_id
where    rh.destination_database_name = @DBName
order by rh.restore_history_id desc
 
--Get last LSN from backup file
declare @logbak table
(
    BackupName nvarchar(128)
    , BackupDescription nvarchar(255)
    , BackupType smallint
    , ExpirationDate datetime
    , Compressed tinyint
    , Position smallint
    , DeviceType tinyint
    , UserName nvarchar(128)
    , ServerName nvarchar(128)
    , DatabaseName nvarchar(128)
    , DatabaseVersion int
    , DatabaseCreationDate datetime
    , BackupSize numeric(20, 0)
    , FirstLSN numeric(25, 0)
    , LastLSN numeric(25,0)
    , CheckpointLSN numeric(25,0)
    , DatabaseBackupLSN numeric(25, 0)
    , BackupStartDate datetime
    , BackupFinishDate datetime
    , SortOrder smallint
    , CodePage smallint
    , UnicodeLocaleId int
    , UnicodeComparisonStyle int
    , CompatibilityLevel tinyint
    , SoftwareVendorId int
    , SoftwareVersionMajor int
    , SoftwareVersionMinor int
    , SoftwareVersionBuild int
    , MachineName nvarchar(128)
    , Flags int
    , BindingID uniqueidentifier
    , RecoveryForkID uniqueidentifier
    , Collation nvarchar(128)
    , FamilyGUID uniqueidentifier
    , HasBulkLoggedData bit
    , IsSnapshot bit
    , IsReadOnly bit
    , IsSingleUser bit
    , HasBackupChecksums bit
    , IsDamaged bit
    , BeginsLogChain bit
    , HasIncompleteMetaData bit
    , IsForceOffline bit
    , IsCopyOnly bit
    , FirstRecoveryForkID uniqueidentifier
    , ForkPointLSN numeric(25, 0) null
    , RecoveryModel nvarchar(60)
    , DifferentialBaseLSN numeric(25, 0) null
    , DifferentialBaseGUID uniqueidentifier
    , BackupTypeDescription nvarchar(60)
    , BackupSetGUID uniqueidentifier null
);
 
insert @logbak exec ('restore headeronly from disk = ''' + @FileName + '''');
 
select     @LastFileLSN = LastLSN
from    @logbak;
 
-- Compare them
if @LastFileLSN < @LastDBLSN
begin
        -- Already got these transactions
end
else
begin
        -- Restore log
end

Idera discontinues SQLSafe Freeware Edition

No longer available for download. Check it out here: http://www.idera.com/Products/Free-Tools/SQL-safe-Freeware-Edition/

Bummer! I’m using this extensively. Now you have to choose between the Enterprise edition and the Lite Edition. The Lite Edition is $295 per instance, which is not quite as attractive a price as free. I suppose it was too good to last. Luckily, I just got the budget approved to upgrade to 2008 R2 Standard, which has native backup compression. Can’t wait!

Backup compression on SQL 2005

Last night, after a lot of preparation, I finally flicked the switch on the backup compression solution I’ve been implementing. The results were dramatic but in a good way:

• backup jobs 60% faster
• 50% disk space saving
• Reporting & BI build 30% faster
• Reporting & BI ready 3 hours earlier than before

Each night my SQL Agent job runs a full backup of the live databases and copies the backup files to a file server folder from where they are later backed up to tape. The last step kicks off another job on my reporting server which restores from the file server’s copy of the backup files and then rebuilds the data warehouse database and Analysis Services cubes. The whole process was only just finishing in time for the start of the business day. This morning it was all over by 04:15.

Backup compression substantially speeds up backups and restores by using more CPU and RAM and less disk I/Os. Because the disk is the bottleneck you get a faster run time and a smaller backup file. SQL 2005 doesn’t have native backup compression, unlike SQL 2008, so you need a third party utility. I used Idera’s SQLSafe Freeware Edition. I have no connection with Idera. I simply spotted the product when I was looking into how much compression might cost. The price was great ($0!), so I tried the product and liked it.

And it all worked first time!

Housekeeping backup and restore history

Every backup and restore event is logged in history tables in msdb and these tables can get out of hand, especially if you’re using log shipping. Here’s one way to sort it out that will catch up with the backlog and then keep your history tables trimmed.

This code will delete the oldest days worth of history as long as it’s more than 30 days ago:

--Delete earliest day of backup and restore history if it's older than 30 days.
--This way you can clear a long history a bit at a time.

use msdb
go

set nocount on

declare @oldest_backup_date    smalldatetime
    , @oldest_restore_date    smalldatetime
    , @oldest_date        smalldatetime

--Find the oldest backup date (can be null)
select    @oldest_backup_date = min(backup_finish_date) 
from    backupset with (nolock) 
where    backup_finish_date is not null

set @oldest_backup_date = isnull(@oldest_backup_date, '01-JUN-2079')

--Find the oldest restore date (can be null)
select    @oldest_restore_date = min(restore_date) 
from    restorehistory with (nolock) 
where    restore_date is not null

set @oldest_restore_date = isnull(@oldest_restore_date, '01-JUN-2079')

--Determine the earlier of the two dates
if @oldest_restore_date < @oldest_backup_date set @oldest_date = @oldest_restore_date else set @oldest_date = @oldest_backup_date print 'Oldest date is ' + convert(varchar(10), @oldest_date, 103) --If it's more than 30 days ago delete it if @oldest_date >= dateadd(day, -30, getdate())
begin
    print 'no action'
end
else
begin
    print 'deleting history'
    --sp_delete_backuphistory deletes everything earlier than @oldest_date.
    --Add 1 day otherwise it won't delete anything.
    set @oldest_date = dateadd(day, 1, @oldest_date)
    exec sp_delete_backuphistory @oldest_date
end

Before you implement it though, I advise you to add some indexes to msdb. Normally I wouldn’t screw around with system tables but I made an exception here. These tables are poorly indexed and adding some indexes makes so much difference. I found this post on Geoff Hiten’s blog and brought the script up to date:

-- msdb performance indexes for SQL Server 2005
-- History:
-- 12/12/2005 Geoff N. Hiten Created (http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx).
-- 06/07/2009 David Wimbush   Commented out indexes that Microsoft have implemented.
--                            Added an index on restorehistory restore_date.

use msdb 
go 

--backupset 

--implemented by Microsoft
--create index IX_backupset_backup_set_id on backupset(backup_set_id) 
--go 
--implemented by Microsoft
--create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) 
--go 

create index IX_backupset_media_set_id on backupset(media_set_id) 
go 

create index IX_backupset_backup_finish_date on backupset(backup_finish_date) 
go 

create index IX_backupset_backup_start_date on backupset(backup_start_date) 
go 


--backupmediaset 

--implemented by Microsoft
--create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) 
--go 


--backupfile 

--implemented by Microsoft
--create index IX_backupfile_backup_set_id on backupfile(backup_set_id) 
--go 


--backupmediafamily 

--implemented by Microsoft
--create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) 
--go 

 
--restorehistory 

--implemented by Microsoft
--create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) 
--go 

--implemented by Microsoft
--create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) 
--go 

--NEW:
create index IX_restorehistory_restore_date on restorehistory(restore_date) 
go 

 
--restorefile 

create index IX_restorefile_restore_history_id on restorefile(restore_history_id) 
go 

 
--restorefilegroup 

create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) 
go