Create SQL script that create database and tables

Question

I have a SQL database and tables that I would like to replicate in another SQL Server. I would like to create a SQL script that creates the database and tables in a single script.

I can create "Create" script using the SQL Management Studio for each case (Database and Tables), but I would like to know if combining the both "Create" scripts into single script would be enough.

Thanks.

Answer 1

Although Clayton's answer will get you there (eventually), in SQL2005/2008/R2/2012 you have a far easier option:

Right-click on the Database, select Tasks and then Generate Scripts, which will launch the Script Wizard. This allows you to generate a single script that can recreate the full database including table/indexes & constraints/stored procedures/functions/users/etc. There are a multitude of options that you can configure to customise the output, but most of it is self explanatory.

If you are happy with the default options, you can do the whole job in a matter of seconds.

If you want to recreate the data in the database (as a series of INSERTS) I'd also recommend SSMS Tools Pack (Free for SQL 2008 version, Paid for SQL 2012 version).

Answer 2

In SQL Server Management Studio you can right click on the database you want to replicate, and select "Script Database as" to have the tool create the appropriate SQL file to replicate that database on another server. You can repeat this process for each table you want to create, and then merge the files into a single SQL file. Don't forget to add a using statement after you create your Database but prior to any table creation.

In more recent versions of SQL Server you can get this in one file in SSMS.

1. Right click a database.
2. Tasks
3. Generate Scripts

This will launch a wizard where you can script the entire database or just portions. There does not appear to be a T-SQL way of doing this.

Answer 3

An excellent explanation can be found here: Generate script in SQL Server Management Studio

Courtesy Ali Issa Here's what you have to do:

1. Right click the database (not the table) and select tasks --> generate scripts
2. Next --> select the requested table/tables (from select specific database objects)
3. Next --> click advanced --> types of data to script = schema and data

If you want to create a script that just generates the tables (no data) you can skip the advanced part of the instructions!

Answer 4

Not sure why SSMS doesn’t take into account execution order but it just doesn’t. This is not an issue for small databases but what if your database has 200 objects? In that case order of execution does matter because it’s not really easy to go through all of these.

For unordered scripts generated by SSMS you can go following

a) Execute script (some objects will be inserted some wont, there will be some errors)

b) Remove all objects from the script that have been added to database

c) Go back to a) until everything is eventually executed

Alternative option is to use third party tool such as ApexSQL Script or any other tools already mentioned in this thread (SSMS toolpack, Red Gate and others).

All of these will take care of the dependencies for you and save you even more time.

Answer 5

Yes, you can add as many SQL statements into a single script as you wish. Just one thing to note: the order matters. You can't INSERT into a table until you CREATE it; you can't set a foreign key until the primary key is inserted.