SQL Server 2012: Generate Scripts from command line

Question

I am running SQL Server 2012.

The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts.

Is there a way to automate that via command line somehow?

I want to create a script that includes the schema and data of the entire data base.

Tools like ScriptDB and sqlpubwiz.exe all seem to target SQL Server 2005. What about SQL Server 2012?

Answer 1

Best is to use Powershell - if you are going to use it frequently. You can refer to Automated Script-generation with Powershell and SMO.

Also, SQL Server PowerShell Extensions (SQLPSX) are of great value when working with Powershell. All the modules are having help files e.g. Get-SqlScripter.

Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path D:\scripts\script.sql
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlView | Get-SqlScripter

For third party tools, highly recommend to check out (there are many third party tools out there, but below ones I have used and they are great):

• Redgate's SQL Compare and Data Compare
• SSMS Tools Pack by Mladen Prajdić

Answer 2

Tara Raj from Microsoft recently announced that the Microsoft SQL team has released a set of command line tools to generate T-SQL scripts that appear to do exactly what you've asked:

mssql-scripter

Mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS.

You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse. You can save the generated T-SQL script to a .sql file or pipe it to standard *nix utilities (for example, sed, awk, grep) for further transformations. You can edit the generated script or check it into source control and subsequently execute the script in your existing SQL database deployment processes and DevOps pipelines with standard multiplatform SQL command line tools such as sqlcmd.

Mssql-scripter is built using Python and incorporates the usability principles of the new Azure CLI 2.0 tools. The source code can be found on Github at https://github.com/Microsoft/sql-xplat-cli, and we welcome your contributions and pull requests!

Some examples of use:

Generate DDL scripts for all database objects (default) in the Adventureworks database and output to stdout

$ mssql-scripter -S localhost -d AdventureWorks -U sa

Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file

$ mssql-scripter -S localhost -d AdventureWorks -U sa –schema-and-data  > ./output.sql

Answer 3

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

Answer 4

Just an update: in current versions of SQL Server powershell modules (SQL Server 2014 and on, I believed. Tested on SSMS 17), most of these options are native commands and methods.

For instance, you can use Get-SqlDatabase and methods such as .Script() and .EnumScript(). It's really useful and simple, especially if you want a more granular approach (specific tables and other objects).

For example, this will generate CREATE scripts for user defined functions and save it to file:

$Database = Get-SqlDatabase -ServerInstance $YourSqlServer -Name $YourDatabaseName

$MyFuncs = $Database.UserDefinedFunctions | Where Schema -eq "dbo"
$MyFuncs.Script() | Out-File -FilePath ".\SqlScripts\MyFunctions.sql"

If you want to script data and elements like indexes, keys, triggers, etc. you will have to specify scripting options, like this:

$scriptOptions = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions

$scriptOptions.NoCollation = $True
$scriptOptions.Indexes = $True
$scriptOptions.Triggers = $True
$scriptOptions.DriAll = $True
$scriptOptions.ScriptData = $True

$Database.Tables.EnumScript($scriptOptions) | Out-File -FilePath ".\AllMyTables.sql"

Note that the Script() method doesn't support scripting data. Use EnumScript() for tables.

A single table:

($Database.Tables | Where Name -eq "MyTableName").EnumScript($scriptOptions)

All your views, save one file per each view, DROP and CREATE script:

ForEach ($view in $($Database.Views | Where Schema -eq "dbo")) {

    "`nIF OBJECT_ID('$($view.Name)') IS NOT NULL DROP VIEW $($view.Name);`n`n" | Out-File -FilePath ".\SqlScripts\$($view.Name).sql"    
    $view.Script() | Out-File -FilePath ".\SqlScripts\$($view.Name).sql" -Append
}

Hope this helps.

Answer 5

You can use the command line versions of xSQL Schema Compare and xSQL Data Compare. With the command line versions you can schedule periodic data and schema comparisons against an empty database and this always have the scripts to build an exact replica of the latest version of your database from scratch.

Disclosure: I'm affiliated to xSQL.