Sql-server – How to copy a SQL Server Database Schema and Objects between servers using VS2019

sql servervisual studio

I've inherited a database with about 1500 tables, and 3600+ objects overall. I want to copy the schema – and just the schema – across to a different server, where I can get it into source control (TFS), and generally work out what to do with the various bits of it. Here is what I have tried so far:

  1. Schema compare and update target using VS2019. Even using a reasonably specced development machine, VS became unresponsive.

  2. Tasks > Generate Scripts in SSMS. The source database uses 28 different filegroups that are not available on the destination server. So far as I can see, it's not possible to turn off the setting to include filegroup when generating the script.

  3. SSMS Copy SQL Server Objects task. Again failed because of the filegroup issue.

  4. VS2019, new database project, and Import Database. This pulled all 3600 objects across like a charm, but there are thousands of build errors. This being the case, I can't publish it. I set the build action = none for all the objects, but then it didn't deploy anything either.

Is it possible to deploy a database project that won't build? It doesn't seem an unreasonable request. Given that I have not yet been granted eternal life, it seems unlikely that all the build errors will get fixed. But meanwhile, I would like it to be under version control, and to be able to use the bits of it that do work.

I could create the filegroups on the destination server, and, to get myself out of a hole today, I probably will. But I am a big fan of database projects, and would like to be able to make use of the rich versioning and comparison features it offers.

Best Answer

not exactly database project solution, but might be able to help you out with the filegroups if you don't find any other way.

dbatools offer this function - Export-DbaScript.

With that, you could try to export any object you want using SMO.

# set the option to not include Filegroup information
$options = New-DbaScriptingOption
$options.NoFileGroup = $true

# get all the tables from MyDatabase on the localhost
# generate script using $options defined above
Get-DbaDbTable -SqlInstance localhost -Database MyDatabase | Export-DbaScript -ScriptingOptionsObject $options
Related Question