Table archiving
-
Is there a way to archive old data from tables in SQL server? I have several tables that have timestamped records added daily and we want to remove those records when they reach 18 months old. We can't just delete these rows because of the potential need to access them in an emergency.
-
No automatic way. I guess partitioned tables could work, but it's up to you.
One thing that might make sense is creating another database, duplicate the table and then copy the old data using a job to move it to the new database and delete it from the old one. You could schedule this to run weekly or monthly and work on a rolling 18 month window.
-
John
Depending on why you want to remove the records, another thing that might work for you is this. Create a flag column in your table called IsActive, with a default of 1. Set up a job to run however often you like that sets the flag to 0 for all rows with a date greater than 18 months old. The drawback of this scheme is that you have to amend all your queries to include a WHERE IsActive = 1 clause. However, it does mean that all your data is in the right place when that emergency occurs.
John
-
You can set up a transaction. The ArchiveTable is identical to the main table (no identity) with an Archive_Date.
set transaction isolation level repeatable read
begin tran
declare @refDate datetime
set @refDate = dateadd(month, -18, getdate()
insert into ArchiveTable
select *, getdate() from MainTable
where myTimeStamp <= @refDate
delete MainTable
where myTimeStamp <= @refDate
commit transaction
Russel Loski, MCSE Business Intelligence, Data Platform
-
in the above job the expression used for move should be myTimeStamp <= @refDate if I am not wrong?
-
Thank you for correcting my error. I will fix it in the original.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Another option is to use MS Access to keep a copy of the archived data. I know it's not SQL Server, but this works very well for me. I need to keep the current state each month of all my lookup tables, as well as some data tables, and I have a couple of Access databases that I use for this. A big advantage is that it is very easy to view and work with this data in emergencies, and it can always be reloaded to SQL if necessary. It's very portable, and the Access databases can be zipped and burned to CDs for easy reference.
I have tables in the Access database that are formatted the same as the SQL tables. For each new table, I make a link to the SQL table, and then create a make table query to copy the data. I then change that to an append query, and save it. This query is used each month to copy the data (after deleting the previous data). I have a simple VBA script that does the deleting and copying. Click one button on a form, and it all happens automatically.
You could set it up to append just the relevant records on a regular basis, then run the deletes from either Access or SQL. You could also add error checking, record counts, etc. Be careful to make sure the datatypes are set correctly for the backup tables, as there are some differences between Access and SQL, but these are usually not a problem.
Another suggestion, similar to the first one made by Steve Jones, is to make a "history" table for the data in the same SQL database. Move the archived data to this table. This might be easier to use if you need to access the data frequently, but it won't help if you are trying to reduce the size of your database.
Steve Brokaw
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply