Solved

Large Table Move between Database

Posted on 2009-07-01
6
168 Views
Last Modified: 2012-05-07
We have a requirement where we need to move few very Large tables from Main database to another new database on the same server.
There are 2 tables having more then 300 Million records.. What's the best way to move tables from one database to another db. Its SQL Server 2005.
0
Comment
Question by:Aanvik
  • 4
  • 2
6 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24755082
use SSIS, it is a quick and easy way to set this up to do this...it uses BULK INSERT, which is key for you in this scenario.

To do it, right click the source db, go to tasks, andthen export data.  from there, follow the wizard....save the package at the end so you can use it again if you need to.
0
 
LVL 5

Author Comment

by:Aanvik
ID: 24756331
Its taking too much time. Don't we have any better approach.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24756602
No
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24756604
when you created the table on the destination db, did you make sure that you DID NOT copy the indexes as well?  
0
 
LVL 5

Author Comment

by:Aanvik
ID: 24756628
I just created the Primary key index.. Not other indexes.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24756913
you're going to be better off if you don't have that...especially if the PK is not an identity column...
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 57
SQL Agent Timeout 5 47
Order by but want it in specific order 2 26
Selection from table2 where criteria for table1 10 31
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now