Copying the entire contents of the database (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 completely in a script, since I cannot transfer files to the target web server. How to use Microsoft SQL Server Management Studio, how to do it?

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

Answer 1

You can script the schema and database data using SSMS

Right click on the database. Select tasks .... then select generate sql scripts.

Specify all objects. Then in the options menu, select below to generate scripts for the data.

Below are links that will be useful.

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 it to any instance. Since I don’t like to post “how” when I found this out somewhere else initially - here MSDN describes the process.

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

That is how we do things in our production environment. We back up and restore in other cases.

Answer 3

The easiest way is to restore from a backup, but since you cannot copy files to the file system, the database copy wizard will do this.

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