0

I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below:

enter image description here

Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many .UserDefinedFunction.sql files.

I'm going to make a batch script that goes to each folder and runs sqlcmd on every file in that folder.

Does the execution order matter here, and do I have the right order?

1
  • Shouldn't you let your source control system do / manage this for you? Why do you want to manually manage 1700 scripts? Seems you only create more work and (as already mentioned) more problems.
    – SMor
    Mar 24 at 12:57
0

The execution order absolutely matters, and you shouldn't change it from what was generated. It is likely you have dependencies in one script on objects that need to be created first from another script. For example, if one of your Stored Procedures use a View then it will fail upon generation because it can't find the View it references. This is why generating scripts puts them in order by dependency instead of object type.

Only in the very rare case where all your scripts don't have dependencies on each other (or your re-ordering is coincendentally in order of dependency still), then you'd be ok.

8
  • The generated order was alphabetical so the file name with lower letter came first no matter if it was table or procedure or function etc.
    – Jam
    Mar 24 at 12:41
  • @Jam I'm not sure how you generated your scripts (as SSMS usually generates only 1 script file per database, and it's dependency based), but regardless, if you have dependencies in some of your script files, on objects that don't exist yet (such as my example in my answer) then that script will fail. If you're saying you manually generated a script for each individual object in alphabetical order, then you're definitely going to run into a dependency problem when you try to run them. You should generate scripts at the database level instead if you need a large portion of it.
    – J.D.
    Mar 24 at 12:49
  • I purposefully chose to generate one script per object because when I chose single script file it and tried to run it it got errors and I couldn't open the script file in a text editor because it was 60 GB. There errors were many of Unclosed quotation mark after the character string ''. and I couldn't know how to locate the errors in a 33 million line file.
    – Jam
    Mar 24 at 12:56
  • 2
    @Jam, seems like you are scripting data also with script options, I would suggest you to have a single file for the "Schema Only" option, which will take care of the dependency and multiple files for the "Data only" option.
    – Biju jose
    Mar 24 at 14:07
  • 1
    @Bijujose That's a great point, that's likely the source of his 60 GB big script files and "unclosed quotation" error. Scripting just the schema should result in a file a few MBs big at best lol.
    – J.D.
    Mar 24 at 14:14
0

It's better to use a dacpac to do the same thing in a proper way.

https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15

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.