SQL Server Data Archive Solution

Question

I'm looking for a solution to archive data that exists in my database. My database is SQL Server 2008 and have approximately 250 tables. I search web and find the below link : http://www.dbazine.com/sql/sql-articles/charran13/

but this solution is not clear

I don't know that is there a direct way for doing this in sql server. please give me a solution.

Answer 1

The solution that you choose to rollout will be dependent on your reasons for implementing data archival.

For example you will need to consider:

1. What data is to be archived?

   For example all data or data older than a certain date?

   This has implications for the most suitable archive techniques to be used, i.e. whether a full database backup could suffice or perhaps a purpose built archiving process using SQL Server Integration Services may be more suitable.

2. How accessible does the archived data need to be?

   Does the archived data need to be immediately available, perhaps on slower SATA storage or perhaps a period of several days to acquire access to the data is acceptable? Perhaps if the restore time is quite liberal then a FULL database backup, taken at regular intervals, say once a month could suffice. If on the other hand the data needs to be very much on hand then a solution.

3. What are the security implications required by the data?

   For example, should backups be encrypted or if data is being stored offsite is there adequate physical security in place?

Perhaps if you could provide some more details as to your reasons for wishing to archive your data and also how much of your data you wish to archive, then we may be able to provide more specific guidance.