0

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.

4
  • the buikd errors are usually cascading, so that fixing one will resolve many, also you always the same error, so that you have already a solution ready, the rest will fall into place when you search SO.
    – nbk
    Nov 29 '20 at 15:09
  • Check, if any of these solutions will make schema compare work for you: stackoverflow.com/questions/47527192/…
    – Piotr
    Nov 29 '20 at 15:56
  • Any reason why you can't search & replace the 28 different filegroups & replace with the 1 target one ? Nov 30 '20 at 13:21
  • Try using sqlpackage.exe to generate your projects via CLI - more reliable than VS/SSMS for big tasks. Nov 30 '20 at 21:41
1

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
0

I'll be honest with you, as a developer I would've thought I'd like Database Projects in Visual Studio but as a DBA I hated them. There were some stupid hurdles with how Microsoft designed them, in my opinion.

I believe if your main objective is source control of the database, then a solution specifically crafted for and built into the database system is best such as what's offered from RedGate or ApexSQL. Both systems work pretty seamlessly and connect to Git and TFS as needed.

In regards to migrating the database schema, I'd say you can either use a 3rd party migration tool like SQL Examiner which is relatively cheap and has worked very well for me in the past. Or you can utilize SQL Server's built in Data Tier Application Dacpac File to move just the schema. Dacpacs are relatively simple to create and use and you can find a good walkthrough here: An introduction to Data-Tier applications in SQL Server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.