question

shawnhenson avatar image
shawnhenson 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?
databaseexport-datasystem-databasesexportdata-export
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
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?
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
JohnM avatar image
JohnM answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image
Venkataraman answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.