My technical skills
My picture album
My code library
Search my site
Sign my guestbook
SQL Server Articles New
How to archive data from
my production database? Is there a tool or command built-into SQL Server for this?
You may not always be archiving just a single table. You need to understand how different tables in the database are related to each other through foreign key relationships and then, sequence your delete/archive process, so that you are not violating any of the constraints.
Here's an example scenario of
archiving all the orders taken more than 6 months ago, from the Orders and
OrderDetails tables. The following stored procedure will copy
orders older than 6 months into similar tables of a different
database called Archive, and delete those rows from the main database:
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
You just need to call this stored procedure without any parameters, to get rid of all orders older than 6 months. As you can see, you can pass an optional date parameter, to customize the archiving process. Care is taken not to delete orders placed in the last three months. Depending on your requirements, you can schedule this stored procedure as a job, using SQL Server Agent, so that it runs every week or fortnight.
This type of jobs tend to block the table while they are running. So, it's better to schedule these jobs for off-peak hours. Also, keep an eye on the transaction log space utilization. In case of any issues with log space, consider archiving in smaller batches.
Here's one thing you should consider: If you are using transactional replication to maintain a reporting database, data will be deleted from the reporting databses also, once you run the archival stored procedure. If that's not what you wanted (that is, maintain complete data in the reporting database), then you might want to consider publishing stored procedures, instead of tables, and customize the archiving stored procedure on the subscriber, so that it does nothing.