April 7, 2010 at 12:15 am
Hi Experts,
I am poor into coding. Please help me if anyone can..
Scenario: need to archive a table in a database which is more than 60 GB. Table has only 5 columns and where for every 6 months it increases to 40GB+. here it stores XML data.
Help required on automating the same for every 6 months to Different server + different database.
Table : MessageList
Columns : Msg_id,Msg_time, Msg_sender_id, Msg_subject, Msg_data
based on msg_time - ex:2009-07-07 15:15:59.000 , i would like to archive the data of more than 6 months (ex:@CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP))
This has to be automated to different server and different database with the same table name for evry 6 months.
Attached is the file which is modified (extracted from portal) but not sure, if it works...
Anyone let me know how to correct and help me to do so.
-Win.
Cheers,
- Win.
" Have a great day "
April 7, 2010 at 5:58 am
- Win. (4/7/2010)
Anyone let me know how to correct and help me to do so.
Have you tested it ? i dont think you did.
Script is looking OK but better to test it in a testing environment.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 7, 2010 at 9:52 am
But how to archive from one instance to another instance.
Any script code for that to archive the data from one instance to another...
Cheers,
- Win.
" Have a great day "
April 7, 2010 at 2:03 pm
On your source instance create a linked server to you archive instance. Then reference your linked server in the insert statements.
April 8, 2010 at 2:00 am
Good point, thanks. tried this as well...
But i have to use in script, thats the criteria..
Cheers,
- Win.
" Have a great day "
April 8, 2010 at 3:14 am
- Win. (4/8/2010)
But i have to use in script, thats the criteria..
INSERT INTO LINKEDSERVER.Databasename.dbo.Tablename
Select * from DatabaseName.dbo.Tablename
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 8, 2010 at 11:59 pm
🙂
Thanks Bhuvanesh,
This inserts the data from one table to another table (instance).
But this is not a part of archiving. I knew we can insert the data into other table. The thing is it has to verify the date of > 6 months data and delete in first table and insert into second table.
I need 6 months data to be available in table1 after archiving.
-Win.
Cheers,
- Win.
" Have a great day "
April 9, 2010 at 3:24 am
i think you can do one thing
1. Add a column in source table "archive_flag"
2. set archive_flag = 1 for six month data
3 inset data into dest. table where archive_flag = 1
4. delete source table where archive_flag = 1
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply