Importing a database without the use of VS or ADS

[chadbaldwin]

Apologies ahead of time if this is the wrong place to ask this, I wasn't sure where else it should go. I've placed it in the "Ideas" category, because as far as I can tell, this feature doesn't yet exist.

So in short...I'm trying to find a way to automate the process of exporting a SQL Server database into a folder format in the same way that Visual Studio SSDT and ADS SQL Projects do. When you import a database from a DACPAC or SQL Server Instance database...it will automatically create the folder structure and import everything needed into the project.

Including things like Triggers, their order, database level items, permissions, etc.

I cannot find how to do this for the older VS SSDT SQL Projects, but at the same time...that sounds like it would be a pain anyway because you'd still need to find a way to generate and populate the .sqlproj file.

Now that we have SDK style projects which don't require a full list of included files, and the project files can be created using a dotnet new template...I'm hoping that we'll eventually see the ability to also automate/script the process off importing a DACPAC or SQL database.

Are there any future plans to support something like this? Or maybe already support for it now?

A use case would be - a large company with lots of databases and they want to automate the process of creating and importing SQL projects. Rather than manually creating a new project in ADS and importing the DB, it could be scripted in PowerShell as a way to quickly bootstrap their migration to SQL projects.

[dzsquared]

hi! In good news - this is possible.
The functionality you're looking to do (take a database schema out into files) is the SqlPackage extract command with the /p:ExtractTarget property set to ObjectType, Schema, or SchemaObjectType.

sqlpackage.exe /Action:Extract /TargetFile:{filename}.sql /DiagnosticsFile:{logFile}.log /SourceServerName:{serverFQDN} /SourceDatabaseName:{databaseName} /SourceUser:{username} /SourcePassword:{password} /p:ExtractTarget:File

From the extract docs:

Specifies alternative output formats of the database schema, default is 'DacPac' to output a .dacpac single file. Additional options output one or more .sql files organized by either 'SchemaObjectType' (files in folders for each schema and object type), 'Schema' (files in folders for each schema), 'ObjectType' (files in folders for each object type), 'Flat' (all files in the same folder), or 'File' (1 single file).

As you noted, with the SDK-style projects we can add a template to the NuGet for easily instantiating a new project. Unfortunately, this hasn't been completed just yet.

cc: @zijchen

[chadbaldwin]

Wow, how did I not see that in the docs. I've spent hours just on that page alone. Oh well, thanks for pointing that out.

After quite a bit of tinkering, I finally got it to work...there seems to be a bug in the code though. It says:

The TargetFile argument must refer to a file with a '.dacpac' extension.

Even though it's exporting to a directory. So I changed it to be "DatabaseName.dacpac" and it created a directory named "DatabaseName.dacpac"....but it appears to have created it with all the files and objects like I was hoping to get.

So I can work around the weird naming issue.

Also, this only seems to work in the most recent version of sqlpackage. It took a while to figure that one out too. I had to test it against every installed version I have on my computer:

$list = gi ($Env:ProgramFiles, ${Env:ProgramFiles(x86)}, "${Env:ChocolateyInstall}\bin") |
    gci -Recurse -Filter sqlpackage.exe -ErrorAction SilentlyContinue |
    select FullName, @{N='FileVersion'; E={$_.VersionInfo.FileVersion}}

$list | sort -Property FileVersion -Descending | % {
    Write-Host 'SqlPackage Path:', $_.FullName
    Write-Host 'SqlPackage Version:', $_.FileVersion

    & $_.FullName `
        /a:Extract `
        /scs:"{REDACTED}" `
        /TargetFile:"$($_.FileVersion).dacpac" `
        /p:ExtractTarget=SchemaObjectType

    Write-Host ''
    Write-Host ''
}

I have 5 versions installed and it only worked on the latest one:
✅16.0.6161.0
❌16.0.5400.1 - this failure may be related to something else. This was installed via chocolatey, and returns an odd dependencies error
❌15.0.5472.2
❌14.0.4127.1
❌13.0.3450.1

[chadbaldwin]

@dzsquared just an FYI, in addition to the bug mentioned above...I wasn't sure where to either submit the fix myself, or report the issue, but it appears the new ExtractTarget property is missing from the .NET documentation page:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.dacextractoptions

The page exists, it's just not listed as a property on the above's page:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.dacextractoptions.extracttarget

It's also missing from the side navigation tree. Looks like it's missing its "Applies to" assignment.

[dzsquared]

we were finally able to resolve the clog in the API docs publishing that left it in a half-published state, it would finally be rendered correctly with the extracttarget property on the dacextractoptions page and treeview for 160.

[chadbaldwin]

Looks great, thanks!