Create script of database and all objects using tsql


  • I need to create the script of database and all objects using tsql. Please help with this.
    Wednesday, May 30, 2018 3:24 AM

All replies

  • Hi, 

    You can use Script Database Wizard.   Right click on Database > Tasks > Generate Scripts > Choose Objects .   Add all your objects.  I recommend to go to Advanced in Set Scripting Objects Pane to customize the script you want to generate like Check for Object Existence.   Hope this helps you.



    • Proposed as answer by Sanjeev4646 Thursday, May 31, 2018 6:17 AM
    Wednesday, May 30, 2018 4:02 AM
  • Hello sanjeev,

    I have a multi tenant system. For a tenant, I need to exactly copy the master database. When the tenant runs the application, the database and all objects must be created in its server. If I have a tsql, I will execute it from application and the database and all objects would be created.

    Or is there any other way to automate the copy of master database in the tenants' server? Any process which would automate the copy would be helpful. 

    Thank you.

    Wednesday, May 30, 2018 4:13 AM
  • Hello,

    Do as Sanjeev suggested, use the script to create an template database with all objects, but withof user data, create a backup of the database, then you can restore the backup for every new tenant.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, May 30, 2018 5:50 AM
  • Hi Curendra, 

    I'm not sure if you can exactly copy the master database as the only way to replace the master or recreate is to take instance in single user mode and restore with replace. You cannot replace master database objects through scripts.  

    You can script above action (Restore master database ) and try to automate but this can be challenging as you need to Stop SQL Instance > Start it in single user mode > Restore the master database copy .   I'm pretty sure the success factor is quite less through 3rd party applications or script if you do this.    


    Wednesday, May 30, 2018 5:52 AM