SQL SERVER – 2008 – 2008 R2 – 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.

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard1

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard2

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard3

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard4

Pay attention to the option Types of data to script – select option ‘Schema and data’

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard5

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard6

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard7

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard8

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard9

As the file with data will be very large, use SQLCMD to execute the large script which will create database with schema & data.

SQL SERVER - 2008 - 2008 R2 - Create Script to Copy Database Schema and All The Objects - Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects dswizard10

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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Video – Best Practices Analyzer using Microsoft Baseline Configuration Analyzer
Next Post
SQL SERVER – What Kind of Lock WITH (NOLOCK) Hint Takes on Object?

Related Posts

122 Comments. Leave new

  • Pablo Morixe
    May 31, 2013 12:10 am

    Hello, i need to make the same action ( generate script ) but, i need to do it using t-sql (without using the graphical environment)..

    that’s possible?

    thanks

    Reply
  • Irtekaz Ahmed Khan
    June 26, 2013 3:04 pm

    Thanks,but please sir database change name problem to another server script run time

    Reply
  • i didn’t generate script of my live database.
    Its showing error

    Reply
  • Thank you verrymuch. Nice picture guide.

    Reply
  • Bhavik Thakkar
    August 17, 2013 10:09 am

    Hi,

    I am facing one problem while trying to generate Database script of one database,

    i have tried different servers with 4 core & high configurations, its simple database & i am just trying to generate script of Procedure,Functions & Triggers not including tables & data, still its taking too long (approx more than 2 hours) to generate script.

    Enviornment : SQL Server 2008 R2

    * This problem is for specific one database only, in all other database its working good.

    Can anyone please help me ?

    Reply
  • very nice . Thanks you all

    Reply
  • it doesnt script the Triggers (table level) nor Stored Procedures. nor Functions.
    have you explored the options at individual object level ??

    Reply
  • hi…i want to generate script with Master Key,Certificates and symmetric keys in sql server 2008…

    Reply
  • Hi, I am using sql server 2008 with 2 types of db. But in one db can’t generate scripts.it is showing error like “Index is out of the boundary”. but i tried another db. it is generating scripts good. what is that exact issue? do u know?

    Reply
  • After creating the script if i run that script on another computer then it is giving me error like can not find mdf file. do this script does not create new database into the specified path

    Reply
  • Namith Krishnan E
    April 25, 2014 10:52 am

    Is there any way to generate this script with data using query? Please Help…..

    Reply
  • Is there a way to generate script of limited number of rows and not all the data?

    Reply
  • Nagendra Kumar
    July 1, 2014 10:51 am

    Sir, Please tell me after creation script file .sql format then how to restore again if script file so large.

    Reply
  • Hi! I have a query and it is as follows:

    I wish to create the table structures in excel and create tables in SQL Server 2008 R2. Could you please guide ?

    Reply
  • i have problem with this
    1st : i using option ( script to file ) for table contain large data
    2nd : now when i try to copy the script for this table to another table its give to me an error message and stooped me to do that
    so how can i solve this problem , how can i copy this large script to new table

    Reply
  • Can We Take script of Single table with Data?

    Reply
  • Can we do this without SQL wizard? do we have some way to do it using TSQL? if not all the data I want to replicate entire schema including indexes, defaults, partitions etc.

    Reply
  • Hi,

    Is there any way to generate database schema and with complete data using sql query without using sql wizard.

    Reply
    • Why do you don’t want to use tools available? If you want script then you can use SMO or PowerShell also.

      Reply
  • How would I copy schema [dbo] into same database [dbobackup]? Also, why does my work want me to do that?

    Reply
  • My company wants me to copy the data in the default database schema [dbo] into the same database with a new schema called [dbobackup]. Why would they want to do this? Most importantly: How would I do this?

    Reply

Leave a Reply

Menu