| Author |
Topic |
|
Frozen_123
Starting Member
33 Posts |
 Posted - 2007-11-06 : 19:16:33
|
| Hi all,I have a table in xyz database and there is no column in table like creation_date or modified_date.The problem is I want to delete records which has been added in the table before 1st jan 2007. The size of table is 85 GBImmediate help would be appriciable.Regards,Frozen |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-06 : 19:24:24
|
| Restore a backup of that database from a backup dated 12/31/2006.Otherwise you would have to do it manually, since there is no information in the table that would identify when the records were added.Is there a customer number or anything where it is at least sequential? Perhaps you can find the cutoff point or something. |
 |
|
|
Frozen_123
Starting Member
33 Posts |
Posted - 2007-11-06 : 19:38:22
|
| Thanks for reply,If I restore xyz database through 12/31/2006 backup . What abt the other objects like procedures, logins etc which I have created after 12/31/2006.Regards,Frozen |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-06 : 19:45:23
|
| All of them. If the backup is dated 12/31/2006, nothing created after that date would be there..common sense.You CAN restore it to a DIFFERENT database name however, OR just restore that table using partial restore. I recommend a full restore to a different database name ( a new one that yu will create and fill with the restored data). Check BOL for the syntax, but you can restore xyz to xyz_2 for example and then just replace the table in xyz with the one from xyz_2 (or delete the records from xyz that are not in xyz_2 from the restore) |
|
|
|
Frozen_123
Starting Member
33 Posts |
Posted - 2007-11-06 : 19:50:13
|
| I think the best way to create the script of the objects which has been created after 12/31/2006.Then Restore the database through 12/31/2006 backup and then execute the script. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-06 : 19:59:46
|
| That would be wise as well, but scripting objects won't replace DATA. Only structure. Scripting the entire database may be a good idea, that way you have everything structurally. What you propose is fine for procedures and such, but you will lose everything post 12/31/2006 if you just restore to that point.In a similar situation, I would restore to a different DB so I could control what is lost/gained or otherwise screwing the pooch. Make sure you have a good back up of up-to-date info before you delete any post 12/31/2006 data. |
|
|
|
Frozen_123
Starting Member
33 Posts |
Posted - 2007-11-06 : 20:02:49
|
| Thanks,I got the solution.Regards,Frozen |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2007-11-07 : 14:44:02
|
quote:
Originally posted by Frozen_123
Thanks,I got the solution.Regards,Frozen
Does that solution also include adding a date column to avoid this oroblem in the future? |
|
|
|
Frozen_123
Starting Member
33 Posts |
Posted - 2007-11-07 : 18:42:53
|
| Well table is very big so I'll have to do lots of work if i add creation_date column in the tableor if u have any solution for this??? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-07 : 18:54:09
|
| If you aren't using select * in your code and are explicitly listing the columns in selects and inserts, then adding the column with a default constraint should not impact anything.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Deletion