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?
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