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

  • How come when I try to generate a script, the table triggers are not generated along with the table schema? And is there a way that it could be included automatically?

    Reply
  • Mohamed Abubucker
    March 2, 2016 5:12 pm

    Sir… am getting one error during generating script that error is “Could not read metadata, possibly due to insufficient access rights” how to solve this? assist me thanks in advance…

    Reply
  • Chandrasekhar
    June 7, 2016 10:56 pm

    Exactly, I need to generate this script daily or multiple times a day. So how to achieve this objects script without doing manually. Please help me and your help is highly appreciated

    Thanks,
    Chandra

    Reply
  • Chandrasekhar
    June 7, 2016 11:07 pm

    Hi I am chandrasekhar,

    I want create script only not data for all objects such as tables, views, sp’s, functions and all related keys. i want to
    exactly what have done using tasks and script wizard.
    I have been waiting for your response please…..

    Reply
  • Is there a way to so this with SQL code rather than using the wizard? I want to drop all stored procedures in a database and recreate them from another database.

    Reply
  • I can’t get this to compile when copying/pasting. Can you attach a word doc or email to me?

    Reply
  • I want to import data from another database into an empty copy of it. Can I do that with this script and does it take relational constraints into account so that it imports the data into parent tables before populating the child tables dependent on their Primary Keys?

    Reply
  • How to backup DataBase which is Live MS SQL

    Reply
  • Very helpful, thanks. Do you know if this can be done through a stored procedure? without any manual interaction

    Reply
  • Camilo Castilla
    November 18, 2016 9:02 am

    How can I generate the script from commands, ie without having to enter the script generator?

    Reply
  • Hi pinal,

    thanks for above solution, i am trying to generate the script using SMO but it wont work. it generate the redudent script..

    Reply
  • Hari Natha Reddy
    May 16, 2017 12:23 pm

    Hi Pinal,

    Kindly help to generate individual objects data base wise by the way of automation sql job.
    Am having limited access to server every month needs to generate scripts for 10 databases, it’s very difficult to me every time to get access.

    Thank in advance

    Reply
  • Robert Everett
    January 21, 2018 1:55 am

    We have two databases – a live and a dev. I have created the script for a table (schema, data, sprocs, etc) that I wish to copy to the dev.

    How do I copy it to the dev database ?

    Reply
  • Is there any way to automate or create a sql job to run on weekly basis?

    Reply
  • sadiq hirani
    May 4, 2018 12:43 pm

    I am having different issue…

    my database is hosted on server having 2016 ver

    i am using 2014 ver locally

    i am having issues with restore locally

    and need to generate script remotely

    so as to get it synced locally

    is there any solution?

    Reply
  • Hi,

    Is there a way to automate sql server schema backup (no data)?

    I have a 700GB database that needs to be backedup(schema only). This way, when i need to restore a table structure,I dont have to restore 700GB data.

    Thanks,
    Krishna

    Reply
  • Hi Pinal,

    What collation sequence would the columns in the new database have in this case? Would it be the same as those in the source database, or would it be the default collation sequence for the target server? Is it easy to force the columns to have the same collation sequence as the target server (and not those of the source database)? If this doesn’t happen by default, can you recommend a method of editting the very large script file so as to force the collation sequence to be the default collation sequence of the target server?

    Thanks
    Richard.

    Reply

Leave a Reply

Menu