Archiving Data in SQL Server 2005

Archiving data is one of the very big topics with lots of challenging. I am trying to give small light on this topic for newbie. In our day to day activity we often don’t need very old data may be few year old data. Question might arise in mind, what should we do for those data? Delete it? Transfer it somewhere else? Deleting wouldn’t be solution in every company as you might need to view those data after few years so better option to move that somewhere in else.

You can archive your data to the same database or else you can create separate database with identical same structure and use it as your archived database. I would prefer second option as it removes the overhead on the same database.

Let us create one simple stored procedure which will archive your data to another table in same database and delete the data from the original table if archiving will done successfully.

--create table for demo

use adventureworks

go

CREATE TABLE ArchiveTest

(

ID INT IDENTITY(1,1) NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

--CREATE table two for archiving

CREATE TABLE ARCHIVE_ArchiveTest

(

--NOTE: I didn't define ID column as IDENTITY as we are going

-- to archive the records and we can't enter manual value in IDENTIY column

ID INT NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

--insert records in above table

INSERT INTO ArchiveTest(Name)VALUES('Ritesh')

INSERT INTO ArchiveTest(Name)VALUES('Rajan')

INSERT INTO ArchiveTest(Name)VALUES('Dharmesh')

--create stored procedure to Archive data

CREATE PROC ArchiveFromTest(@StartCharOfName CHAR(1))

AS

BEGIN

SET NOCOUNT ON

BEGIN TRAN

INSERT ARCHIVE_ArchiveTest

SELECT * FROM ArchiveTest WHERE Name LIKE 'R%'

IF @@ERROR<>0

BEGIN

ROLLBACK TRAN

RETURN

END

DELETE FROM ArchiveTest WHERE Name LIKE 'R%'

IF @@ERROR<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRAN

RETURN

END

--CHECK whether above stored procedure works

EXEC ArchiveFromTest 'R'

--finally check your data

SELECT * FROM ArchiveTest

SELECT * FROM aRCHIVE_ArchiveTest

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com