How do I use Source Control with SQL Server Management Studio?

Question

How do I use Source Control with SQL Server Management Studio?

Answer 1

My solution is only version history. I have a scheduled powershell script that generates the definitions of specified objects (stored procs, functions, tables, and views). It then uses git to update a GitLab repository. Git will only update if there are changes in the file contents so it is okay to generate all of the scripts each time.

function SQL-Script-Database
{
    <#
    .SYNOPSIS
    Script all database objects for the given database.

    .DESCRIPTION
    This  function scripts all database objects  (i.e.: tables,  views, stored
    procedures,  and user defined functions) for the specified database on the
    the given server. It creates a subdirectory per object type under 
    the path specified.

    .PARAMETER savePath
    The root path where to save object definitions.

    .PARAMETER database
    The database to script (default = $global:DatabaseName)

    .PARAMETER DatabaseServer 
    The database server to be used (default: $global:DatabaseServer).

    #>

    param (
        [parameter(Mandatory = $true)][string] $savePath,
        [parameter(Mandatory = $false)][string] $database = $global:DatabaseName,
        [parameter(Mandatory = $false)][string] $DatabaseServer = $global:DatabaseServer
    )

    try
    {
        if (!$DatabaseServer)
            { throw "`$DatabaseServer or `$InstanceName variable is not properly initialized" }

        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

        $s = New-Object Microsoft.SqlServer.Management.Smo.Server($DatabaseServer)
        $db = $s.databases[$database]

        $objects = $db.Tables
        $objects += $db.Views
        $objects += $db.StoredProcedures
        $objects += $db.UserDefinedFunctions

        $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

        $scripter.Options.AnsiFile = $true
        $scripter.Options.IncludeHeaders = $false
        $scripter.Options.ScriptOwner = $false
        $scripter.Options.AppendToFile = $false
        $scripter.Options.AllowSystemobjects = $false
        $scripter.Options.ScriptDrops = $false
        $scripter.Options.WithDependencies = $false
        $scripter.Options.SchemaQualify = $false
        $scripter.Options.SchemaQualifyForeignKeysReferences = $false
        $scripter.Options.ScriptBatchTerminator = $false

        $scripter.Options.Indexes = $true
        $scripter.Options.ClusteredIndexes = $true
        $scripter.Options.NonClusteredIndexes = $true
        $scripter.Options.NoCollation = $true

        $scripter.Options.DriAll = $true
        $scripter.Options.DriIncludeSystemNames = $false

        $scripter.Options.ToFileOnly = $true
        $scripter.Options.Permissions = $true

        foreach ($o in $objects | where {!($_.IsSystemObject)}) 
        {
            $typeFolder=$o.GetType().Name 

            if (!(Test-Path -Path "$savepath\$typeFolder")) 
                { New-Item -Type Directory -name "$typeFolder"-path "$savePath" | Out-Null }

            $file = $o -replace "\[|\]"
            $file = $file.Replace("dbo.", "")

            $scripter.Options.FileName = "$savePath\$typeFolder\$file.sql"
            $scripter.Script($o)
        }
    }

    catch
    {
        Write-Error "`t`t$($MyInvocation.InvocationName): $_"
    }
}

# repeat line for each database/server to be pushed to version control 
SQL-Script-Database C:\Users\UserName\DbObjDef\FolderName DatabaseName ServerName

cd C:\Users\UserName\DbObjDef
git add .
git commit -m "nightly diff" 
git push -u origin master

Answer 2

SQL database versioning directly from SSMS can be accomplished using various 3rd party add-ins. For example ApexSQL Source Control has seamless SSMS integration and it can be used not just with TFS, but the following SC systems are supported out of the box: Git, Subversion, Mercurial, Perforce. Team Foundation Server support includes an online version, Visual Studio Team Services.

As explained in this article, the ApexSQL Source Control add-in introduces, amongst other things, an IDE called the Action center tab allows you to maintain your source control repository without necessarily having to first script out database objects. Usually the Action center tab will automatically be opened as soon as you finish linking a database into a source control repository but you can still manually open it through keyboard combination keys Ctrl + Alt + A or by right clicking the source control-linked database and navigating to the Action center option.