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 bi开发者_JAVA技巧t & 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 ...?

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

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.

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.

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).

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!