Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
manju3606
Yak Posting Veteran
78 Posts |
 Posted - 2011-12-26 : 03:51:29
|
| Hi to all,I have database with 3.5 years of data.And user want to archive data from databse but i have to create condition or policies or any other thing witch cant allow user to archive all data or less then 2 years of data. User can ony archive more then 2 years of data.for example in my database 2008,2009,2010,2011 years of data availaible but user can only archive from Jan 2008 to Aug-2009 bcoz in my database 2 years of data should be available. So for this what i have to do i am not understanding so please could you help me with this.ThanksManju |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-26 : 04:24:51
|
| 1) Delete data older than 2 years2) Move data older than 2 years to a separate table / database3) Create data in a table specific to the "current year" and use horizontal partitioning (which will provide you with the equivalent of data archiving - you can physically delete old/stale data by DROPPING tables of data which is older than required) |
 |
|
|
manju3606
Yak Posting Veteran
78 Posts |
Posted - 2011-12-26 : 04:38:25
|
| Hi Kristen,Thanks fot reply, i cant use partitioning bcoz end user can archive data from table to xml or csv or new database just i want to restrict user to he cant archive below 2 years of data.Thanks Manju |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-26 : 05:39:45
|
| Just make sure that the application does not allow a date within the last two years.I don't know what you mean by "archive". Typically that is either DELETE stale data, MOVE stale data somewhere else, or STORE data (at the outset) in separate table so that it is segregated.If you are talking about EXPORT and then DELETE then you just need to prevent the DELETE on data less than 2 years old.You can use a TRIGGER to prevent ANY data being deleted that is less than 2 years old, but that would not allow a row [under 2 years old] to be deleted under ANY circumstances, and there may be reasons in the application where that could happen normally? If not use a trigger.Personally I would just build a 2-year-date-validation it into the application where the user makes the "Export" to XML / CSV |
|
|
|
manju3606
Yak Posting Veteran
78 Posts |
Posted - 2011-12-27 : 08:11:51
|
| Hi Kristen,I am talking about EXPORT and then DELETE data from table but i want to restrict user, he cant archive below 2 years of data from that table . user can only EXPORT data of more then 2 years, 2.years of data should be available in table.ThanksManju |
|
|
|
manju3606
Yak Posting Veteran
78 Posts |
Posted - 2011-12-28 : 03:22:17
|
| Hi visahk,could you please help me for my above postManju |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-12-28 : 03:31:45
|
quote:
Originally posted by manju3606
Hi Kristen,I am talking about EXPORT and then DELETE data from table but i want to restrict user, he cant archive below 2 years of data from that table . user can only EXPORT data of more then 2 years, 2.years of data should be available in table.ThanksManju
Isnt the suggestion given below by Kristen more than enough ?quote:
Personally I would just build a 2-year-date-validation it into the application where the user makes the "Export" to XML / CSV
PBUH |
|
|
|
|
|
|
|
|
Data Archive