MSSQL - Create Script to Copy Database Schema and All The Objects – Data, Schema, ...
 

MSSQL - Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects



Quite often I get the request regarding how to copy all the objects – including schema and data from any database and re-create it on another instance.
SQL Server 2008 and SQL Server 2008 R2 has script generator wizard which does it for us. I ask you to pay special attention to image #5.
After the script is generated, the next challenge often users face is how to execute this large script as SQL Server Management Studio does not open the file. One can use SQLCMD for the same. See that in the last image of this post.


http://www.pinaldave.com/bimg/dswizard1.jpg
http://www.pinaldave.com/bimg/dswizard2.jpg
http://www.pinaldave.com/bimg/dswizard3.jpg
http://www.pinaldave.com/bimg/dswizard4.jpg
Pay attention to the option Types of data to script – select option ‘Schema and data’
http://www.pinaldave.com/bimg/dswizard5.jpg
http://www.pinaldave.com/bimg/dswizard6.jpg
http://www.pinaldave.com/bimg/dswizard7.jpg
http://www.pinaldave.com/bimg/dswizard8.jpg
http://www.pinaldave.com/bimg/dswizard9.jpg
As the file with data will be very large, use SQLCMD to execute the large script which will create database with schema & data.
http://www.pinaldave.com/bimg/dswizard10.jpg
Please let me know if you have any question or suggestion on this blog post.


Watch SQL in Sixty Seconds Episode on same subject.
Reference : Pinal Dave (http://blog.SQLAuthority.com)


:battay: