Sql-server – SQL Server 2012: Generate Scripts from command line

scriptingsql server

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?

Best Answer

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):

Related Question