Identify and Delete SQL Server Archive Tables for Rollback

By: Jeremy Kadlec | Updated: 2007-12-27 | Comments | Related: More > DBA Best Practices

Problem

In an recent tip (Backing up SQL Server Data for Rollback Purposes), the second option in the tip outlined steps to backup data prior to making a mass data changes. This is a critical process in order to rollback if an issue arises. Unfortunately, one missing component in that tip is identifying and deleting unneeded archive tables. Depending on the deployment schedule, the amount of archived data can quickly add up, which expands backup\restore windows, increases tape\storage costs and may lead to a situation where unneeded data lingers for months or years. Although I want to have a solid rollback plan, I also need to be able identify and delete the archive tables once they are 30 days old. Any suggestions on how to do so?

Solution

Being able to identify and delete archived tables can be accomplished as long as you have a consistent set of rules. The first rule is a consistent naming convention i.e. 'zBck_%'. The second rule is the retention decision which identifies how long the table should be retained. One way to identify the table is by a string in the table name i.e. 'zBck_30Days_%'or a consistent retention period such as 30 days for all tables. Based on these two decisions, scripts can be built to meet these needs. Let's jump into these three scripts:

Identifying Old Archive Tables - Fixed Number of Days

USE AdventureWorks;
GO

DECLARE @RetentionPeriod int
SET @RetentionPeriod = 30

SELECT [Name], Principal_Id, Schema_Id, Type_Desc, Create_Date, Modify_Date
FROM sys.all_objects
WHERE [Name] LIKE 'zBck_30Days_%'
AND Type_Desc = 'USER_TABLE'
AND DATEDIFF(day, Create_Date, GETDATE()) > @RetentionPeriod
AND DATEDIFF(day, Modify_Date, GETDATE()) > @RetentionPeriod;
GO

Dropping Old Archive Tables - Generated Scripts

USE AdventureWorks;
GO

SET NOCOUNT ON

DECLARE @RetentionPeriod int
SET @RetentionPeriod = 30

SELECT 'DROP TABLE ' + '[' + S.[Name] + ']' + '.' + '[' + O.[Name] + ']' +
char(13) + 'GO' + char(13)
FROM sys.all_objects O
INNER JOIN sys.schemas S
ON O.Schema_Id = S.schema_id
WHERE O.[Name] LIKE 'z%'
AND O.Type_Desc = 'USER_TABLE'
AND DATEDIFF(day, O.Create_Date, GETDATE()) > @RetentionPeriod
AND DATEDIFF(day, O.Modify_Date, GETDATE()) > @RetentionPeriod;

SET NOCOUNT OFF

Dropping Old Archive Tables - Automated Script

USE AdventureWorks;
GO

SET NOCOUNT ON

-- Declaration statements for all variables
DECLARE @SchemaName nvarchar(128)
DECLARE @TableName nvarchar(128)
DECLARE @RetentionPeriod int
DECLARE @CMD1 varchar(5000)

-- Initialize the values
SET @RetentionPeriod = 30

-- Populate the cursor
DECLARE ObjectNameCursor CURSOR FOR

SELECT S.[Name], O.[Name]
FROM sys.all_objects O
INNER JOIN sys.schemas S
ON O.Schema_Id = S.schema_id
WHERE O.[Name] LIKE 'z%'
AND O.Type_Desc = 'USER_TABLE'
AND DATEDIFF(day, O.Create_Date, GETDATE()) > @RetentionPeriod
AND DATEDIFF(day, O.Modify_Date, GETDATE()) > @RetentionPeriod;

OPEN ObjectNameCursor
FETCH NEXT FROM ObjectNameCursor INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

-- Drop table command
SELECT @CMD1 = 'DROP TABLE '+'['+@SchemaName+']'+'.'+'['+@TableName+']'+char(13)
-- SELECT @CMD1
EXEC (@CMD1)

FETCH NEXT FROM ObjectNameCursor INTO @SchemaName, @TableName

END

CLOSE ObjectNameCursor
DEALLOCATE ObjectNameCursor

SET NOCOUNT OFF
GO

Additional Considerations

Another consideration for managing the archived tables are to store them in a separate database i.e. ArchiveDatabase. This will help to keep the production databases free of archived objects and may help to minimize the tape/storage costs. Nevertheless, the archive database should still be backed up based on the addition of new objects in case the data is potentially needed for a 30 day period.

Next Steps

When setting up an archive process, be sure to incorporate a deletion plan in order to maintain a reasonable set of objects so they do not linger for months or years. One table can have a long term tangible cost, so not managing archive tables can be costly.
- On a related note, once team members turn over, the tables may never be deleted because the new team is concerned about ever deleting any data because it may be needed. So build a set of rules and follow it.
Be sure to have a reasonable set of rules so that the deletion process can be managed and automated so that databases do not get bloated and the process is not manually intensive.
If you have an existing archive process be sure to incorporate a deletion process and consider the scripts from this tip.
Check out these related tips:

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2007-12-27

Identify and Delete SQL Server Archive Tables for Rollback

Problem

Solution

Next Steps

About the author

Comments For This Article