Copy complete SQL Server 2008 db

Question

In order to deploy my application I would like to copy db schema and content from my devel maschine to a production server. Even better, I'd like to be able to regularly communicate changes in the devel db to the production db by completely resetting the production db to an exact copy of the devel machine.

With mysql this would be easily accomplished by dumping the prod db to a file and loading the dump into the target db. Unfortunately, I couldn't find similar functionality on my SQL Server (Express). The import/export functions I have been finding either only concern the schema, not the data, or they work on one table at the time, meaning that they don't copy relationships between tables.

Copying a complete db would seem like a common problem so I hope there is a solution out there. Any advice?

Cheers, Duffy

Answer 1

generate the scripts: right click dev db tasks- generate scripts next (if welcome screen appears) ensure correct db is selected and click the scheckbox next to "script all objects int he selected database" change any options as necessary (ie, set the script data option to true) click next click next click finish click close copy and paste generated scripts on target server (you may have to delete and recreate the db on the target server)

Another option is to purchase Redgate's (or any similer tools) SQL Compare and SQL Data compare. it'll tell you what's different and allow you to generate the scripts to only update what has changed.

Answer 2

For a simple way of copying a database in sql server you will need to do backup and restore.

3 Steps:

1.)Steps on how to backup the database http://msdn.microsoft.com/en-us/library/ms187510.aspx

2.) In target server create a new database.

3.) Restore database. Right click on the newly created database and follow these steps... http://msdn.microsoft.com/en-us/library/ms177429.aspx

And you are done!

If you want a real-time synchronization, you might wanna try replication... :)

Try reading this thorough documentation of replication. Check this out this will surely help you :)

http://technet.microsoft.com/en-us/library/ms151198.aspx

Answer 3

There are a number of options.

1. Detach the db on dev machine, copy the db files to prod and then attach db.
2. Make a full backup of dev db and restore the backup on prod.
3. Use the Copy Database wizard in Microsoft SQL Server Management Studio.