Question

  Asked: Jul 2 2008   10:24 AM GMT
  Asked by: Roops


Archiving and Purging Data


SQL Server administration, SQL Server archiving

We deal with a very large database that grows at an avrage of 50GB a month. The current database size is 700 GB. All the transaction tables are horizontally partitioned to seperate filegroups assigned to a individual data files holding each months data. What would be the best strategy to retain another archive server that holds data prior to 1year and retain the production server with the recent 1 year data?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



Your current technique seams sound. I would recommend sticking with the existing method, and simply moving the data from the current server to the archive server using DTS or SSIS on a daily basis. Then on the server with the current years worth of data delete all data which is over on year old.

Are you using SQL 2000 or SQL 2005? If you are using SQL 2005 are you using the native table partitioning or manual partitioning?

From R:
You might find this interesting
http://technet.microsoft.com/en-us/magazine/cc160974(TechNet.10).aspx
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Jul 2 2008  8:29PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 

Roops  |   Jul 10 2008  4:46AM GMT

i am using SQL 2005. i have created partitions using partition functions and partition scheme with corresponding data file to hold each months data. i have used a numeric partition key. All the transaction tables has this key in the table design. The backend procedures are also coded based on this partition key..