Moving Email from one database to another
|
Logged in as: Guest
|
|
Users viewing this topic:
none
|
|
Login  | |
|
Moving Email from one database to another - 7.Apr.2006 12:36:47 PM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Hi,
Can someone tell me if its possible (sure it is) too move email from one database to another as I have created mulitple databases to accomodate rapid growth and need to move email corresponding to a date range to a new database.
Cheers
|
|
|
|
|
RE: Moving Email from one database to another - 8.May2006 4:05:40 AM
|
|
|
thesheriff
Posts: 55
Joined: 20.Oct.2005
From: Leicester
Status: offline
|
You should use the Database Configuration Tool on the Configuration page.
This allows you to create multiple databases that the software will switch to automatically, based on an elapsed time frame.
It also allows you to set certain databases as "active" so all new mail will be archived there.
There is no need to worry about moving mails from one DB to another
Cheers
Dom
|
|
|
|
|
RE: Moving Email from one database to another - 3.Jul.2006 5:16:52 AM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Right well ity seems my idea of quarterly databases is not going to work the last qtr database has ended up at 45gb!! this is too large so it look slike i'm going to have to go to monthly databases.
Now this also presents a problem as users regularly access the last 3-6months of email so I need to be able to split the last qtr into 3 so I need to know if anyone knows the sql script to do this? I want to remove the emails from the databse a month at a time and move them into new databases.
|
|
|
|
|
RE: Moving Email from one database to another - 5.Jul.2006 3:42:35 PM
|
|
|
cfreyman
Posts: 10
Joined: 7.Jun.2006
Status: offline
|
Idealshopping,
I too have been trying to do this. Here is my post: http://forums.gfi.com/SQL_Scripts_for_Email_by_year/m_900734566/tm.htm
I've since called GFI and have their developers looking at it to give me a 100% solid answer. My DBA had questions about the script proposed in my post, but we never got any answers from the forums. Let me know if you hear anything and I'll do the same!
Thanks
|
|
|
|
|
RE: Moving Email from one database to another - 6.Jul.2006 9:33:55 AM
|
|
|
zbilic
Posts: 19
Joined: 9.Dec.2005
Status: offline
|
If you find anything out can you please post it here in the forum? I have similar situation where I need to move some emails between databases.
Thanks
|
|
|
|
|
RE: Moving Email from one database to another - 6.Jul.2006 11:35:57 AM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Will do ... if I find anything out!!
|
|
|
|
|
RE: Moving Email from one database to another - 6.Jul.2006 5:45:47 PM
|
|
|
cfreyman
Posts: 10
Joined: 7.Jun.2006
Status: offline
|
I have tried going the support route, but they have been less than helpful to say the least. Its a wonder why we paid for such quality support. In any case, my DBA thinks he may have a way to do it, but needs to talk to someone who might know a little more about the schema and marc DB. Do either of you have a skilled DB that he might exchange ideas with?
|
|
|
|
|
RE: Moving Email from one database to another - 10.Jul.2006 9:54:46 AM
|
|
|
Nicks
Posts: 2741
Joined: 17.Mar.2003
Status: offline
|
Hi,
Rodion had posted a script which allows you to delete emails based on various fields in the database. The script can be easily modified to move emails from one database to another. The script can be found at http://forums.gfi.com/m_130027100/mpage_1/tm.htm#130027100
Note that these scripts are not supported by GFI Software Ltd. Use and modify at your own risk
_____________________________
Nicholas Sciberras
GFI Software - www.gfi.com
Messaging, Content Security & Network Security Software
|
|
|
|
|
RE: Moving Email from one database to another - 11.Jul.2006 3:43:47 PM
|
|
|
cfreyman
Posts: 10
Joined: 7.Jun.2006
Status: offline
|
Can you please provide the easily modified script then? My DBA says that this script is only deleting data with a subset of arc table ID's. There is nothing in the script which inserts data into an empty archive copy of the database. In order to move data to the empty archive the incrementing primary key in the arc table would have to be disabled for the data to be meaningful.
We must be missing something here, can you please modify this "easy" script to allow data to be moved to a new database?
|
|
|
|
|
RE: Moving Email from one database to another - 12.Jul.2006 3:35:01 AM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Same here I have a DBA/Developer currently looking at this 'easily' changed script! and not finding it quite as easy as you suggessted he has nearly got it we just have some testing to to.
|
|
|
|
|
RE: Moving Email from one database to another - 12.Jul.2006 11:49:59 AM
|
|
|
cfreyman
Posts: 10
Joined: 7.Jun.2006
Status: offline
|
Sounds good, let me know if you'd like to collaborate with our developers!
|
|
|
|
|
RE: Moving Email from one database to another - 12.Jul.2006 11:59:55 AM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Will do
|
|
|
|
|
RE: Moving Email from one database to another - 13.Jul.2006 5:15:49 AM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Final script test later this morning then I will post.
|
|
|
|
|
RE: Moving Email from one database to another - 18.Jul.2006 6:53:21 AM
|
|
|
Idealshopping
Posts: 76
Joined: 6.Apr.2006
Status: offline
|
Ok so took longer than I thought. Other things got in the way....
Anyway here goes all commments welcome. For background I have used this to extract mail month by month from an existing databse that covered 3 months and move it into 3 new databases the mail in the original databse was not deleted. I will detach and back the original databse up . (just in case!!)
--Script to move data between GFI mail archive databases
--Always back up databases before running any scripts
declare @tbl table
(id int)
--get all ids for a date range
insert into @tbl
select id from arc where month([archdate])=4 and year([archdate])=2006
--Arc Threads
INSERT INTO [DestinationDatabaseName].[dbo].[arc_threads]([thread_id], [topic])
select thread_id,topic from arc_threads
where thread_id in (select thread_id from arc where arc.id in (select id from @tbl))
--Arc
set IDENTITY_INSERT [DestinationDatabaseName].[dbo].arc ON
insert into [DestinationDatabaseName].[dbo].arc
([id], [thread_id], [parent_id], [thread_index], [date], [priority], [size], [full_subject], [attach_count], [encrypted], [signed], [recipients], [archdate], [message_id])
select [id], [thread_id], [parent_id], [thread_index], [date], [priority], [size], [full_subject], [attach_count], [encrypted], [signed], [recipients], [archdate], [message_id]
from arc
where id in (select id from @tbl)
set IDENTITY_INSERT [DestinationDatabaseName].[dbo].arc OFF
--arc users , I just copy over all users, its easier.
--usrid of 0 is created when the database is added through the GFI Interface
set IDENTITY_INSERT [DestinationDatabaseName].[dbo].arc_users ON
INSERT INTO [DestinationDatabaseName].[dbo].[arc_users]([usrid], [name], [hash])
select [usrid], [name], [hash]
from arc_users
where usrid<>0
set IDENTITY_INSERT [DestinationDatabaseName].[dbo].arc_users OFF
--Arc Sources
INSERT INTO [DestinationDatabaseName].[dbo].[arc_sources]([id], [data], [compressed], [signature])
select [id], [data], [compressed], [signature]
from arc_sources
where id in (select id from @tbl)
--arc bodies
INSERT INTO [DestinationDatabaseName].[dbo].[arc_bodies]([id], [texttype], [charset], [body])
select [id], [texttype], [charset], [body]
from arc_bodies
where id in (select id from @tbl)
--arc att
INSERT INTO [DestinationDatabaseName].[dbo].[arc_att]([id], [filename], [attsize], [attid])
select [id], [filename], [attsize], [attid]
from arc_att
where id in (select id from @tbl)
--arc add
INSERT INTO [DestinationDatabaseName].[dbo].[arc_add]([id], [email], [type], [name])
select [id], [email], [type], [name]
from arc_add
where id in (select id from @tbl)
--arc owners
INSERT INTO [DestinationDatabaseName].[dbo].[arc_owners]([id], [usrid], [primary])
select [id], [usrid], [primary]
from arc_owners
where id in (select id from @tbl)
--tidy up afterwards.
--Make sure you have backed up your database before un-commenting this section!!!!!
/*
delete from arc_sources where id in (select id from @tbl)
delete from arc_bodies where id in (select id from @tbl)
delete from arc_att where id in (select id from @tbl)
delete from arc_add where id in (select id from @tbl)
delete from arc_owners where id in (select id from @tbl)
delete from arc where id in (select id from @tbl)
delete from arc_threads
where NOT EXISTS(select id from arc where thread_id=arc_threads.thread_id)
*/
--Script to clear out a database in case you need to start again
--Make sure you have backed up your database before un-commenting this section!!!!!
/*
use [DatabaseName]
delete from arc_sources
delete from arc_owners
delete from arc_users where usrid<>0
delete from arc_add
delete from arc_bodies
delete from arc_att
delete from arc
delete from arc_threads
*/
|
|
|
|
|
RE: Moving Email from one database to another - 19.Jul.2006 2:37:26 PM
|
|
|
cfreyman
Posts: 10
Joined: 7.Jun.2006
Status: offline
|
Thats basically what we did! Thanks again.
|
|
|
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
 Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts |
|
|
Printable Version
