archiving

Hey guys,
I would like to know how sql archiving is written. What is the best approach
to this such as do this, don't do this stuff? Sample codes will be very
helpful too.
Thanks.
Neil
Neil
Look at Vyas's example
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
"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:eaaruzCvEHA.3948@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I would like to know how sql archiving is written. What is the best
approach
> to this such as do this, don't do this stuff? Sample codes will be very
> helpful too.
> Thanks.
> Neil
>