How to Move Old Data From one Data Base To another Database(All Tables)

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi..
Im Working On Some 'X' Project, It is Running From Last 6 Years, Now I Want To Split Data Into Two Data Base. i.e 2009 to 2012 Data In one Db & from 2013 to another Database
Plz Help.. Thanks In Advance.

Posted 13-Mar-14 19:34pm

Tharun@Chinna

Updated 13-Mar-14 19:46pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Maxxx_ · Answer 1 · 2014-03-13T19:49:00

Solution 1

How I would do it:

backup and restore the Db to another Db - so one becomes the 'old' and one the 'new'

identify the transactional tables

write sql to delete by date, < in one Db and >= in the other.

This assumes that you have some sort of transaction date on the transactional data, of course!

Posted 13-Mar-14 19:49pm

_Maxxx_

Comments

Tharun@Chinna 14-Mar-14 2:04am

HI Maxxx,
IN My Db I have 100+ transactional Tables, is Any Alternative to Delete Data with 1 Query, Instead Of Writing Delete Query Foe Each Table. i.e Parent Table X is There. Before Deleting Data from 'X' Table I have to delete Data from All Child Tables, Is it Possible To Delete data from All Parent And Child tables Data At A time..

_Maxxx_ 14-Mar-14 7:24am

If there is a 'dateupdated' column on every table, for example, you could use sp_foreachtable to run 'delete from ? where fromdate < @thedateyouwant
This will fail for referenced tables, but you can just keep running it until it gets rid of everything.
A brute force approach - but should work if you have a consistent method of identifying records on every table.

thatraja · Answer 2 · 2014-03-14T00:01:00

Solution 2

Check these
Archiving Data in SQL Server[^]
Auto Maintain Archive, Purge & Purge on Source Table in SQL Server[^]

Posted 14-Mar-14 0:01am

thatraja