July 13, 2009 at 3:41 am
Hi,
what is the best solution for archiving the data.I cannot keep in the database more than 6 months worth of data, otherwise, the database will get slow. (the company has hundreds of thousands of records per year)
So my question is what's the best method for archiving data, and how can I make it easy for the user to access the archived data, in case they decide to lokkup something that was archived ?
I need a pocedure or script to archive data, using the datecreated column for number of tables. I would like to automate the process for every month.
Can this be possible ???
Can anyone suggest a archive tool if any ??
Thanks.
Cheers,
- Win.
" Have a great day "
July 13, 2009 at 5:33 am
Hi Winslet..
Here is the Stored procedure that hopefully serves what you are looking for....
CREATE PROC dbo.ArchiveData
(
@CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete orders from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @CutOffDate
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16, 1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @CutOffDate
)
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @CutOffDate
)
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @CutOffDate
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
July 13, 2009 at 5:47 am
Thanks Amit,
I have seen the same in one of the sites.... But my query is to transfer data from one server to another server with same database name and after transfering the data older than 8 months then i need to delete the data from main production database.
So that my Prod DB should contain only the data which is of latest 6 - 8 months. I will use the archived one if necessary thru the archived DB in future.. As i will again create another site to access the older data fro archived DB..
Fast help please......... Any ideas reg tools, utilities....??
Cheers,
- Win.
" Have a great day "
July 13, 2009 at 9:48 pm
Any ideas reg tools, utilities....??
Cheers,
- Win.
" Have a great day "
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply