September 26, 2005 at 8:58 am
Do you know some database archiving software for Microsoft SQL Server like Princeton Softech's Archive for Servers?
Thanks in advance.
September 27, 2005 at 9:32 am
For a strat see http://www.sqlservercentral.com/columnists/wbrown/fourofakindbackupsoftwareshootoutpart2.asp as well as part 1 for a detailed description of 4 different software to help with backups
Francis
September 27, 2005 at 9:40 am
Thanks, but I'm looking for archiving database software in spite of backup software ...
September 27, 2005 at 9:51 am
We use ArcServer to copy backups to tape. ArcServe has a SQL option but we don't use it. The backup is just like any other file we backup and doesn't need to be treated differently. The Arcserv SQL Server option actually does the backup. I see no benefit and only problems for doing this.
Francis
September 27, 2005 at 10:24 am
Generally database archiving is using for obtain a useful backup/recovery plan. When you have a big database and the database size is growing continuously (100 GB monthly), it is very hard to do a backup and recovery operations...so you can define a ILM (Information Lifecycle Management) plan ir order to move the old infomation (low data access rate)to the archiving system.
Thanks.
September 27, 2005 at 10:44 am
Are we talking backing up the data here or actually archiving the data out of the database, (removing from main production environment)?
If it is the removal option, I don't know of any software to do it, but it seems you could probably write some stored procedure to do this.
Archiving data when a datetime field is present is quite easy.
Select * into xyz..abc_arch from xyz..abc where id_datetime between '2005-11-01' and '2005-12-01'
On completion execute:
delete from xyz..abc where id_datetime between '2005-11-01' and '2005-12-01'
If anyone wnated the data back, just a reverse of the script.
If anyone wanted to query the data, create a view that incorporates the archived data and the current data. (Note: you can ONLY create an INDEXED view on data in the current database. You can create across two databases).
If there is a lot of data to archive, you could do a loop select something like:
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
select * into xyz..abc_arch FROM xyz..abc WHERE id_datetime < getdate() - 200
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
-- CHECKPOINT ------Or doing BACKUP LOG File
dump transaction xyz with no_log
END
END
SET ROWCOUNT 0
Then do the following for the delete
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
DELETE FROM xyz..abc WHERE id_datetime < getdate() - 200
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
-- CHECKPOINT ------Or doing BACKUP LOG File
dump transaction xyz with no_log
END
END
SET ROWCOUNT 0
September 27, 2005 at 10:50 am
OK Now I understand what you mean. It had never occurred to me that someone could develop generic software for this. We have a very large database were we wanted to archive a portion representing older data to help with performance. We wrote a specific utility to do the archiving. Its so database specific I never thought someone might develop a utility that could work with any database. Sorry I know of no other similar products.
If you wrote your own you need to decide what to do with the old data (we moved it to another database), then you need to decide how to give users access. (We created a 2nd version of the application specifically to access old data. Of course there is still a problem if someone wants both old and newer data, which we would probably have to write something else). Hmmm ... how much is that Princeton's software?
Francis
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply