Copy entire database contents (schema and data)

Question

I need to copy the contents (tables, views, procs, DATA, etc.) of a SQL Server database and copy it to another SQL Server database. I need to do this entirely in a script as I am not able to carry over files to the destination web server. Using Microsoft SQL Server Management Studio, how can I accomplish this?

Note: The database I am copying to will have the same name and be completely empty.

Answer 1

You can script the database schema and data using SSMS

Right click on the database. Choose tasks....then choose generate sql scripts.

Specify all objects. Then in the options menu choose at the bottom to generate scripts for data.

Below are some links that will be helpful

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Answer 2

You can backup the database and restore to any instance you'd like. Since I don't like posting the "how to" when I learned it someplace else originally - here's the MSDN describing the process.

http://msdn.microsoft.com/en-us/library/ms187048.aspx

It is how we do things in our production environment. We back up and restore to our other instances.