CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Asad Khan
Asad Khan

Reputation: 11889

Copy all data from one SQLServer database to another on same machine

I want to copy all data from one database to another which has the same structure. The databases reside on the same machine & on same sql server.

I have googled a bit & have found solutions like this

    INSERT states (statecode, statename)
SELECT statecode, statename
FROM server1.database1.dbo.states

But the problem is they are copying table by table & I have like more then 100 tables. I was thinking that is there a way to copy all of the data at once.

Views & stored procedures all should be copied.

Or should I be looking in some other direction to achieve this ...?

Upvotes: 1

Views: 2772

Answers (4)

marc_s
marc_s

Reputation: 754230

Check out tools like Red-Gate SQL Compare (for structural comparison) and SQL Data Compare (for data content compare). With Data Compare, you can also easily update one database from another (or a database backup, even).

alt text

They're not free - but if you have to do this several times over and over, just the time (not to speak of the hassle) you save yourself will easily outweigh the cost of purchasing these tools. Excellent stuff - highly recommended!

Upvotes: 0

KuldipMCA
KuldipMCA

Reputation: 3149

Use import export wizard to transfer the data from one DB to another DB and use Generate script for the Transfer the Procedure and views.

Upvotes: 1

Phil Hunt
Phil Hunt

Reputation: 8521

If this is a one-time need, use the (Database) > Tasks > Generate Scripts menu option in SQL Server Management Studio.

Upvotes: 1

nonot1
nonot1

Reputation: 2808

Some options:

  1. Use the DB back up and restore tools to just move a big backup file. This is the simplest option.
  2. Slave the 2nd instance off of the 1st. It'll keep it up to date, but can be a pain.

Upvotes: 1

Related Questions