Recommended methods for SQL database scripting

There are numerous reasons why exists the need for SQL database scripting, i.e. transform objects to their T-SQL form. One can say that it comes natural from a need to recreate some objects on another database, some objects are needed to be sent to developers for editing, or at least to keep the scripts as one form of database backup.

Most of use cases where database scripting is needed are best covered with creation of individual scripts per object so the following explanation will address that method.

Generic solution

What average SQL user gets by default is a relatively decent solution for database scripting, schema objects and data if needed using the SSMS and built-in Object Explorer.

In the context menu of the Object Explorer the Generate Scripts option can be found which will open the database scripting wizard.

The wizard guides the user through some customization options. The selection of objects to script out:

The output form and location of the scripted objects, with the Single file per object option to create separate script per object:

And with Advanced options comes the selection of database properties or table related properties like Indexes, Constraints, Primary/Foreign keys, etc. to be included for database scripting, with the option to script out data along with database schema:

Final step of the wizard is scripting confirmation:

If the option to script out objects as separate files scripting output would look like this:

Here we can see a bundle of SQL script files that represent various objects: tables, views, schemas, etc. The user just needs to sort them out.

Generic problems

In order to perform another database scripting, the previously explained process should be repeated every time.

What needs to be mentioned is that data is scripted within the same script file as the table object that contains data. This can be quite inconvenient if for example, some tables contain a large amount of data and we need to send only scripted objects to a developer.

Wouldn’t be nice if scripting SQL database objects could be done with multiple databases at once? If there is a way to sort objects through subfolders by their type and to separate data from objects? Is it possible to automate scripting SQL databases across multiple SQL Servers instances?

Database scripting with ApexSQL Script

ApexSQL Script is a tool specifically designed for SQL Server database scripting and delivers easy answers to previous questions. Here is an example which will explain the possibilities of this tool. We have a case where we need scripted tables, views and stored procedures from three different databases and we need to script out data from only one database.

Upon opening the ApexSQL Script the initial new project configuration window will appear with the option to establish a SQL Server connection to the instance where databases that need to be scripted out are attached.

After establishing connection all databases attached to the target instance will be listed. From this list, databases that we need scripted must be selected with a checkbox.

The Options tab contains a set of options with their description which customize the content of the scripts when database scripting is activated.

Since only a specific set of objects is needed for scripting, i.e. Tables, Views and Procedures in this example, we can use object filtering option located under the Advanced options section. There will be a list of SQL object types where Tables, Views and Procedures should remain selected to limit the scope of loaded objects in the current project environment. Note that the view will show objects for chosen object type from all selected databases where they could be specifically excluded from loading. After that, the Load button will finish this setup and import selected databases with the defined object scope to the main application window.

After loading, in the main application window, the selected databases will form the list and their structure can be seen. This list will allow further scripting customization in a manner that only specific objects can be selected for scripting. For our use case, all objects will remain selected.

Further, some scripted data that is stored only in one of the loaded databases is required. Use the Data button from the main ribbon menu to switch the view to display the entire data structure for every database in the imported list that contains data. This structural separation between schema and data will help to limit the visual noise in some cases where the database list is too long but only some databases contain data. In this view, the data in tables from one of the databases will be selected.

Note that the Data view allows a custom selection of data to be scripted.

With the object specification set, it is time to proceed with the process of database scripting. This is done through a wizard started with the Script button in the main ribbon menu. The first step in the wizard is with options to choose what is going to be scripted, Structure (schema), Data or Structure and Data. Although it is already selected what should be scripted, this is a neat redundancy which could cover several use cases with the same object selection. Use the Next button to continue with the script wizard.

The second step in the wizard will provide choices for database scripting form:

SQL script – will pack all selected objects and data into a single SQL script file per chosen database. The option is convenient for a batch update of another database.

Script Folder – will create scripted database folder with every object scripted in a separate file and arranged through subfolders by object type and store them in a local folder.

Source Control – will create/update scripted database folder with every object scripted in a separate file and arranged through subfolders by object type and store them in a source control repository. The application will check for dependent objects and ask to include them. Source control connection parameters will be required

C# solution – will create a project package containing scripted databases as C# solution and source code which can be imported in another project in some IDE like Visual Studio

Executable installer – will create scripted database package as an executable which can be used to build another database from saved objects

In the example where database scripting is used for backup or development, Script Folder and Source Control options are preferable.

Choosing the Script Folder for database scripting will switch to the next step with dependent objects detection and selection.

In this step, the application will check for dependent objects and ask to include them.

Since all dependent objects are already selected for scripting the expected result will be an empty list.

The final step will show options for scripted database folder output location and some global database options which will be included in scripts – SQL version compatibility, database collation settings and output file and folder mapping.

Configure mapping option allows custom file and folder formatting. Default formatting will create subfolders for every object type, and filenames based on object name and parenting schema.

When these options are set there are two options to finish the database scripting task, the Automation script button which will be explained further and the Create button to proceed with database scripting.

At the end of the database scripting process, the report will be generated with the option to Save it.

As a result the database folders are created with all selected objects and data neatly sorted out and ready for further use.

Automated database scripting

Entire database scripting procedure can be automated via CLI executions. All parameters and object filtering set through ApexSQL Script GUI can be used as CLI option switches. The tool has the option to generate an automation script with the customized parameters included.

The automation script generation can be invoked from the final step of the script wizard as previously mentioned or directly from the main application window located under the Save button in the Home ribbon menu as Save as automation script button.

The automation script dialogue will generate CLI parameters based on the general set of options used for scripting. Automation script can be generated for batch execution by selecting the Batch radio button:

Or for PowerShell execution by selecting the PowerShell radio button:

Argument set can be changed manually using the editor in the automation script dialogue or with options laid out around it will automatically affect the arguments set when checked:

When automation script is executed in its default form (all parameters set with individual switches and no project file used) it is expected that object filtering options previously set will not be respected and entire databases will be scripted. To resolve this situation it is enough to save the ApexSQL Script project and use it in the automation script. This can be done within the same dialogue with the Project file checkbox:

The project file will save all object filtering and scripting options and will replace them as a single project file argument in automation script instead of all individual arguments. It is possible to manually add individual arguments to override options saved in the project file.

Scripting automatically multiple databases across multiple SQL servers

With all of the possibilities of ApexSQL Script, it is possible to create custom PowerShell script to perform multiple database scripting across multiple SQL servers.

Following script represents a working example for the task of scripting out stored procedures, tables and views:

#existence check for Reports, Outputs, or Summaries folders, and if they don’t exist they will be created
function FolderCheckAndCreate
{
    param 
    (
        [string] $folderRoot, 
        [switch] $reports, 
        [switch] $outputs,
        [switch] $summaries
    )

    $locations = $folderRoot
   
    #location will be set using the corresponding switch
    
    if ($outputs -eq $true)
    {
        $locations += "\Outputs"
    }
        
    
    #create none existing folder and return folder’s path
    if (-not (Test-Path $locations))
    { 
        New-Item -Path $locations -ItemType Directory -Force | Out-Null
    }
    return $locations
}

#variables for the current path recognition 
$pathCurrent = (Split-Path $SCRIPT:MyInvocation.MyCommand.Path)


#system’s root folder 
$folderRoot = $pathCurrent


#location where ApexSQL Script is installed
$appLocation = "C:\Program Files\ApexSQL\ApexSQL Script\ApexSQLScript.com"


$executionSummary = "$folderRoot\ExecutionSummary.txt"
Clear-Content -Path $executionSummary

The above list of commands will set environmental variables for application location and target execution output location relative to the PowerShell script location. Following command will read the list of database names and servers which have to be initially populated in a txt file and saved in the same location as the PowerShell script itself. The list has to be populated in a “Server name, database name” form like shown in the example below:

#location of the txt file with server and database names
$locationServersDatabases = "$pathCurrent\servers_databases.txt"


  $stampDateTime = Get-Date -Format "MM-dd-yyyy_HH-mm"

   #log output location  
   $outputsLocation   = FolderCheckAndCreate $folderRoot -Outputs
   $outputName   = "ScriptLog_$stampDateTime.txt"

Next set of commands will extract textual data from saved txt file and convert it to variable value to be used as execution argument:

foreach($line in [System.IO.File]::ReadAllLines($locationServersDatabases))
{

 
   #defining variables for source and destination servers and databases
    $server1   = ($line -split ",")[0]    
    $database1 = ($line -split ",")[1]
   
   
    #defining variables for date stamp and names for all output files
    $cleanServerName1   = ($server1 -replace "\\",".")
    
    
    #Folder path for the backup scripts - has to be used as argument in CLI swithes
    New-Item -Path C:\ApexSQLScript\""$stampDateTime""\""$cleanServerName1.$database1"" -itemtype Directory

Next set of arguments used for execution, following from beginning to end are: SQL server name, database name, exclude script header, include object bitwise set*, create individual scripts per object, script out only structure (schema), check and include dependent objects, script extended properties of tables, output as script folder, set script text encoding, set output script folder name, force overwrite if existing files found, display verbose console messages.

#defining variable for ApexSQL Script CLI switches
    $scriptSwitches = "/server:""$server1"" /database:""$database1"" /no_format_scr_header /inc:28 /in /mode:structure /include_dependent /scr_ex_properties /output_type:script folder /encoding:Unicode /output_name:C:\ApexSQLScript\""$stampDateTime""\""$cleanServerName1.$database1"" /force /verbose"

The rest of the commands will perform application execution and return the execution result:

#initiation of the scripting process     
    (Invoke-Expression ("& `"" + $appLocation +"`" " +$scriptSwitches) >>  "$outputsLocation\$outputName" )
    $returnCode = $lastExitCode

    #error code report
    if($returnCode -eq 0)
    {
        
        "`r`nScrpting of objects is finnished. Return code is: $lastExitCode" >> "$outputsLocation\$outputName"
    }
        
    #an error is encountered
    else   
    {
        "`r`nAn error is encountered. Return error code is: $lastExitCode" >> "$outputsLocation\$outputName"
        "Failed for server: $server1 database: $database1. Return error code is: $lastExitCode" >> $executionSummary
        #the output file will be opened, as an error is encountered         
    }
    
}

if ([System.IO.File]::ReadAllLines($executionSummary).Count -eq 0)
{
    "Scripting was successful for all selected objects" > $executionSummary
}
Invoke-Item -Path $executionSummary

*For more information on how to set object scope for scripting see: How to use bitwise set in ApexSQL CLI

The set of arguments stored in the $scriptSwitches object are used as an example and can be customized based on automated script generated using ApexSQL Script GUI or by following instructions in the article: ApexSQL Script Command Line Interface (CLI) switches.

Conclusion

We explored how ApexSQL Script simplifies the whole Database scripting process when dealing with truckloads of data. We also automated database scripting with CLI executions and Powershell on multiple databases across multiple SQL Servers. We hope, this guide provides a better understanding of the recommended methods for SQL database scripting and helps to address the issues that one typically encounters while working in a conventional way (using SSMS).