Upgrading to SQL Server

Upgrading and existing SQL Server instance to 2008 can be problematic if you have a large MSDB database. If you log ship any databases, or take frequent backups there will be a row created for every back up taken, be it a log backup, a differential or a full database backup. Over time this will grow.

During the installation of SQL Server 2008, the MSDB database gets updated and columns are added to a few tables, including the ones used for recording the backup activity. This means you could be recreating a table with millions of rows; this causes the installer to hang. Sometimes leaving you with a corrup MSDB database.

To work around this, you need to delete some history from the MSDB tables. Microsoft provide a stored procedure for this, but it uses a cursor and is too slow for a production upgrade.

I've created a replacement that deletes from all the appropriate tables, leaving just enough row to continue your log shipping processes. The amount to be retained is userdefinable in units of days.

Before running the procedure you will want to create the following indexes. You do not need to restart with the 'update catalog' option enabled.

CREATE INDEX [media_set_id] ON [dbo].[backupset] ([media_set_id])
GO

CREATE INDEX [restore_history_id] ON [dbo].[restorefile] ([restore_history_id])
GO

CREATE INDEX [restore_history_id] ON [dbo].[restorefilegroup] ([restore_history_id])
GO

Now compile the following stored procedure in the MSDB database:

CREATE PROCEDURE [dbo].[spDeleteBackupRestoreHistory] (@RetentionDay INT)

AS

DECLARE @OldDate DateTime,

@RowCount INT

SELECT @OldDate = DateAdd(dd, @RetentionDay * -1, GetDate())

IF OBJECT_ID('tempdb..#backup') IS NOT NULL
DROP TABLE #backup

CREATE TABLE #backup
(

backup_set_id int PRIMARY KEY,

media_set_id int,

backup_finish_date datetime,

is_exist char(1) DEFAULT 'N'

)

declare @msg nvarchar(2400)

WHILE (1 = 1)

BEGIN

TRUNCATE TABLE #backup

-- 'Preparing...'

INSERT INTO #backup(backup_set_id, media_set_id, backup_finish_date)

SELECT TOP 1000 backup_set_id, media_set_id, backup_finish_date

FROM msdb.dbo.backupset bs

WHERE bs.backup_finish_date < @OldDate

ORDER BY bs.backup_set_id

SET @RowCount = @@RowCount

select @msg = 'Earliest date '+cast(min(backup_finish_date) as varchar(60)) +' rows:'+str(@rowcount) from #backup

raiserror(@msg,1,1) with nowait

IF @RowCount = 0

GOTO _EXIT

BEGIN TRAN

-- 'delete table restorefile'

DELETE FROM msdb.dbo.restorefile

FROM msdb.dbo.restorefile rf

INNER JOIN msdb.dbo.restorehistory rh

ON rf.restore_history_id = rh.restore_history_id

INNER JOIN #backup bs

ON rh.backup_set_id = bs.backup_set_id

-- 'delete restorefilegroup'

DELETE FROM msdb.dbo.restorefilegroup

FROM msdb.dbo.restorefilegroup rfg

INNER JOIN msdb.dbo.restorehistory rh

ON rfg.restore_history_id = rh.restore_history_id

INNER JOIN #backup bs

ON rh.backup_set_id = bs.backup_set_id

-- 'delete restorehistory'

DELETE FROM msdb.dbo.restorehistory

FROM msdb.dbo.restorehistory rh

INNER JOIN #backup bs

ON bs.backup_set_id = rh.backup_set_id

-- 'delete backupfile'

DELETE FROM msdb.dbo.backupfile

FROM msdb.dbo.backupfile bf

INNER JOIN #backup bs

ON bs.backup_set_id = bf.backup_set_id

-- 'delete backupset'

DELETE FROM msdb.dbo.backupset

FROM msdb.dbo.backupset bs

INNER JOIN #backup b

ON bs.backup_set_id = b.backup_set_id

-- 'search media_set_id that not exists to backupset'

UPDATE #backup

SET is_exist = 'Y'

FROM #backup b

INNER JOIN msdb.dbo.backupset bs

ON bs.media_set_id = b.media_set_id

-- 'delete backupmediafamily'

DELETE FROM msdb.dbo.backupmediafamily

FROM msdb.dbo.backupmediafamily bmf

INNER JOIN #backup b

ON bmf.media_set_id = b.media_set_id

WHERE b.is_exist = 'N'

-- 'delete backupmediaset'

DELETE FROM msdb.dbo.backupmediaset

FROM msdb.dbo.backupmediaset bms

INNER JOIN #backup b

ON bms.media_set_id = b.media_set_id

WHERE b.is_exist = 'N'

COMMIT TRAN

END

_EXIT:

DROP TABLE #backup

GO

This procedure deletes batches of 1000 rows to save on the transaction log, and uses RAISERROR to report the progress in real time.