How to create Data Archiving in SQL Server 2005

[Raghu_M]

I need to know how to archive data from particular database when it is growing above 1GB.Also it has to be restored when it was needed.Any Idea

[Mohsiul Haque]

For most of the time Archiving database can be done by using SQL Jobs, I think at first you need to create a stored procedure which will fetch the data and dump into the archive database and then just call it from SQL Jobs with a given schedule.

[Lost User]

There's no "archiving" in SQL Server, there's only "backup" and "replication".

[Mycroft Holmes]

You are asking for a strategy to manage your data, you are not going to get a satisfactory answer in a forum post. Most archiving is done by time stricture, anything that is 2 yo move to the archive database. This type of process requires that you copy (replicate) the data into an identical database and delete it from the production DB. Here you run up against data structure issues, all your chages to production structure needs to be reflected in the archive data. Queries can be written across both databases, but these are specific to archived results. Another strategy is data warehousing your data. You need to do some serious research into your business requirements before deciding on an archiving strategy.

Never underestimate the power of human stupidity RAH