Our Database exists in many different scripts within our source control. i.E. every table has it's own scripts for creating the table, the indexes and so on. When needed, we can rebuild the entire dev-database by starting a powershell script which will run through every script by feeding it to sqlcmd.exe.
This process recently took about 30 to 40 minutes. We discovered that we were using the old 2008R2 Version of sqlcmd, thanks to a mistake in the local path variables, which pointed to the old version. After fixing this the entire process was reduced to about 8 minutes.
I am now wondering if there are any "switches" I might have overlooked which could speed up this process even more.
The Database is currently set to compat level 100 (for stupid reasons), multi_user mode and simple recovery model. The database runs locally and no user will be connecting during the rebuild process.
I did try setting it to single_user mode but ran into an issue. Apperently the sequential process of firering one sql-script after another seems to be too quick(?) for it's own good. Somehow one connection is not closed fast enough before the next one is established.
Are there any other options either on query or database level that could speed up a database rebuild process in this scenario?