locked
Archiving Data in SQL Server RRS feed

  • Question

  • I work with an SQL database that has a lot of data and also procedures that isn't needed for it to operate and that may never be needed; but then again, it theoretically might be needed at some point.

    Is there a way to archive these so that they can be retrieved but do not clutter-up the system in the mean time?

    Jacob


    • Edited by Jacob Wagner Tuesday, September 6, 2011 5:00 PM corrected request
    Tuesday, September 6, 2011 4:44 PM

Answers

  • Do you need to have offline or online access to the data? If the goal is to have data available somewhere just in case if somebody needs to access the old data, simple backup would work just fine. Backup the database and purge old data from the production database after backup. In such case, when you need access to the old data, you will need to restore database from the backup somewhere and query it. Obvious downside is that it would be time consuming as well as it introduces the challenges when you need to produce report that requires the data from the multiple databases.

    In case if you need to provide online access to the old data - for example keep just a few months of production data in the main database and archive everything older in another database - the question is much more complicated. There is no "right" or "wrong" solution in such case - everything depends on your requirements and system architecture. I did the presentation about it during one of the recent SQL Saturday events - you can download the slide deck from: http://aboutsqlserver.com/presentations/ - "Data Sharding in OLTP systems" from SQL Saturday #79. It would give you some ideas about a couple of different approaches how you can architect the system


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by Jacob Wagner Tuesday, September 6, 2011 5:19 PM
    Tuesday, September 6, 2011 5:07 PM

All replies

  • What are you looking for in your post ? Are you after an archiving strategy ? whether you should archive ? If you can provide a little more detail then this will make it easier to get a response.

    Are you wanting to know whether to remove certain stored procs or archive data ?

    Knowing your data requirements from a business side of things will greatly impact your requirement and ability to archive data from your database. The size of your database will also have an impact on your decision based on if your database is experiencing performance issues due to the amount of data.

    Can your application cope with the data not there and if it is required from time to time how do you go about accessing that archived data.


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    Tuesday, September 6, 2011 5:04 PM
  • Do you need to have offline or online access to the data? If the goal is to have data available somewhere just in case if somebody needs to access the old data, simple backup would work just fine. Backup the database and purge old data from the production database after backup. In such case, when you need access to the old data, you will need to restore database from the backup somewhere and query it. Obvious downside is that it would be time consuming as well as it introduces the challenges when you need to produce report that requires the data from the multiple databases.

    In case if you need to provide online access to the old data - for example keep just a few months of production data in the main database and archive everything older in another database - the question is much more complicated. There is no "right" or "wrong" solution in such case - everything depends on your requirements and system architecture. I did the presentation about it during one of the recent SQL Saturday events - you can download the slide deck from: http://aboutsqlserver.com/presentations/ - "Data Sharding in OLTP systems" from SQL Saturday #79. It would give you some ideas about a couple of different approaches how you can architect the system


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by Jacob Wagner Tuesday, September 6, 2011 5:19 PM
    Tuesday, September 6, 2011 5:07 PM
  • If the goal is to have data available somewhere just in case if somebody needs to access the old data, simple backup would work just fine.

    No, I don't need access to it. It just needs to be kept for the proverbial rainy day. This is probably what I will do.

    Thanks,
    Jacob

    Tuesday, September 6, 2011 5:18 PM
  • Yes I agree with replies, you need to create a separate database just in case you need to access the old data in the future,

    create a separate script file that will  move data from your live database to Archive database and delete data from your live database.

    for example you have data from 01/01/2000 to till date

    you can keep data from 01/01/2005 to till date and move older data (< 01/01/2005) to archive database, maintain a table for archive dates and change your stored procedures in such a way that when you pass dates to SP you will check with the dates in Archve date table and if the date is less than the archive date then bring the date from your archive database othewise get date from live database.

    Example SP below, there are so many other thing that you need to take care the below SP is just a example

    create procedure getRecords
    (@FromDt datetime = null, @ToDt datetime = null)
    as
    begin
    declare @ArchFromDt datetime
    declare @ArchToDt datetime
    
    select @ArchFromDt = ArchFromDate, @ArchToDt = ArchToDate from ArchiveTable
    
    if @FromDt <= @ArchFromDt
    select * from ArchiveDatabase.dbo.YOurarchtable
    where DateColumn between @ArchFromDt and @ArchToDt 
    else
    select * from YourTable
    where DateColumn between @FromDt and @ToDt
    
    end
    GO
    
    


    Thanks & Regards Prasad DVR
    Tuesday, September 6, 2011 5:28 PM
  • Yes I agree with replies, you need to create a separate database just in case you need to access the old data in the future,

    Why? Is there some issue with just using a backup as Dmitri sugested?

    Jacob

    Tuesday, September 6, 2011 6:17 PM