asked
Export & remove data then re-access later
Is there a way to take a database, have it export say 3 years of data into its own database. Then remove those 3 years of data from the current to reduce the size? Then lets say If I needed to get to the data from that 3 years, Is there a way to temporarily connect to the 3 year database?
If I was faced with this task, I'd want to consider things like: How big is the database? How fast is it growing? How many tables and rows in those tables are going to be affected? What happens if you remove a row that is referenced by a different table's foreign key? Are you doing this because of a shortage of disk space or are you trying to improve performance? Is table partitioning and row compression another option?
This site runs by you voting. For each helpful answer below, click on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the thumbs up next to that answer.
There are a multitude of ways to accomplish this. For example, you could use SSIS to loop through all of the tables and move the data; you could use native TSQL to do the same with a cross database join, or possibly you could use a backup/restore strategy. Each solution will require some up front work and design but all are pretty reliable. This isn't going to be a simple switch type of solution that will do the work for you behind the scenes. Once the data is in the archive location, you would most likely have to use a cross database join to query that data in relation to the "active" data. There can be some gotcha's with this, but certainly doable. Hope this helps!
Let us assume you have got dbo.TableA in DatabaseA and history column is CreatedDate. 1. First create a new database DatabaseB 2. Load data into TableA of DatabaseB with filter applied as per script below. 3. Repeat the step 2 for other tables DECLARE @HistoricalFilterStartDate datetime, @HistoricalFilterEndDate datetime SET @HistoricalFilterStartDate = -- Specify historical Start value for filter SET @HistoricalFilterEndDate = -- Specify historical End value for filter SELECT * INTO DatabaseB.dbo.TableA FROM DatabaseA.dbo.TableA WHERE CreatedDate > @HistoricalFilterStartDate AND CreatedDate < @HistoricalFilterEndDate
18 People are following this question.
