If you're using SQL Server, then SQL Server projects in VS2015 are a pretty good way to manage the database side of your project. They let you script how your database should look, and let you version control the instructions to create your database, rather than having to keep loads of .bak files hanging around. In this article, I describe the basic process of moving from a database that you have no scripts for, to a SQL Server project that you can deploy at the push of a button.
I'm writing this because I recently started helping out on a project that a friend of mine has been working on for some time now. He's been using SQL Server and has been managing the database side of things by writing code straight to the database using SSMS and then taking periodic backups. There were no scripts, no way of checking for broken references, no way to merge changes, and the only way to get another developer up and running was to have them restore from a .bak file.
Enter, the SQL Server project. It let's you do a bunch of cool things that you can't do without one, like it will alert you if a stored proc is looking at tables which don't exist anymore for instance, or (if you've got the project in source control) it will let you see what files have changed and by how much. It'll also build and deploy your database with one click. Handy stuff, but how do you move from a database that someone has already created, and quickly and (relatively) painlessly convert that into a SQL Server project so that you can move forward?
Well, the first thing you need to do, is open up Visual Studio 2015 and locate the database you want to script out in the "SQL Server Object Explorer" window. When you find it, right click on it, and choose the "Create New Project..." option.
That'll pop up a "Create New Project" dialog, where you can choose a few options. I fill mine out like this and press start:
Pressing start kicks off a process which I guess could take a long time for some massive databases, but in my case was pretty much over before I knew what had happened. Just click "Finish" out of that screen once it's done, and boom, just like that you will find that Solution Explorer is open and pointing at your shiny new SQL Server project. Congratulations.
Now, let's take a look at what we've actually got here. Directly under your solution is your SQL Server project file, and directly under that sits folders for your various schemas. I'm going to go out on a limb here and suggest that if handing round a .bak file has been the only way to manage your database up to this point, then you've probably only got one folder under your project, and that's the "dbo" folder.
If you take a look at what's under that, you'll see that Visual Studio has handily organised your database into serveral subfolders, each representing a type of SQL object. Specifically in my case here, we've got "Functions", "Stored Procedures", "Tables", and "Views".
If you look at the contents, of any of the files under any of these files, you'll essentially find what you'd get in SSMS by right clicking the object and clicking SCRIPT AS CREATE. You should notice too that just like in a code project, each of these auto generated files has a Build Action associated with it in it's properties. In the case of all the auto generated files, this is set to "Build", which means that every time you "Ctrl - Shift - B" to rebuild your project, Visual Studio will evaluate the SQL in the file and flag up any references that don't make sense or are outdated. Errors will pop up in the error window as you will be used to from code projects.
You should now try building your project. Sadly, (and by sadly, I really mean "thank god"), I can't help you sort out the various broken bits of SQL that you're probably seeing in your database right now, so go fix those, and then come back. I'll wait.
Back? Maybe that was painful; maybe you are a saint and that was easy peasy; either way, hopefully your are already sold on the benefits of having Visual Studio check your SQL for you. We certainly were after that step.
"Okay" so you may now say, "my database builds, and is now shiny and error free, but I've got data I rely on being in my database. Enums and test data and stuff".
Yes, yes you do, and that's where Post Deploy scripts come in. They run as you might expect, post deployment, that is to say, after your schema has been built and deployed by Visual Studio. This is the place for static data to go, so let's set up a file to pop some in.
To get a Post Deploy SQL file into the project, simply right click on the project, and choose "Add" and then "Script...". This will pop up a dialog that looks like this:
No prizes will be awarded for guessing which option you should choose to get our Post Deploy Script running, but I've highlighted the correct answer just in case... To just flesh the options on this screen out a bit, a "Script (Build)" will be built and deployed alongside all of the other files in the project like your table and function creation scripts. The "Script (Not in build)" files just pretty much sit there, and only get run if you call them from another file that is getting built (more on this shortly). "Pre-Deployment Script" files get run before the tables and everything else. Honestly, I'm not sure what you'd use these for exactly, but I'm sure whatever it is must be important.
Finally the star of the show in this case is the "Post-Deloyment Script". It gets run after your schema files have built, and like the Highlander, there can be only one. Yes, for some reason you cannot have more than one post deployment script in your project at one time. Presumably for reasons to do with the order that the scripts would get run in.
"But Martin", you say, "surely putting all my static data into one monolithic file is bad practice and a nightmare to maintain?". And you would be right to say that. In practice, you will want to have your static data inserts sorted into files by table, or functionality, or however you feel is best. This is where SQLCMD and the "Script (Not in build)" files from earlier come in to play.
Firstly, add your Post Deployment script in to your project. Then add a "Script (Not in build)" file called "Test.sql". Then open the post deployment file, and enable SQLCMD mode by clicking the following button as expertly pointed out here:
SQLCMD mode lets you do a whole bunch of fancy stuff, most of which I'm honestly not familiar with. What's important for our purposes is that it lets us tell Visual Studio to build a file not otherwise in the build. What we're going to do is write the following code:
So that your post deployment file now looks like this:
This one line tells Visual Studio that at line 13 in the Post Deployment script, it should take the contents of "Test.sql" and execute them, then return to the post deployment file to continue as usual from line 14. This means that you can instruct Visual Studio to execute a limitless number of SQL files as part of the post deployment process by simply adding new lines starting ":r". As long as SQLCMD is enabled, like I showed above, this should enable you to insert static data scripts to your hearts content. I recommend keeping all the static data files together in a new folder, maybe excitingly called "StaticData", so that it's obvious to everyone what they are. Obviously if you do that, the path that you point to from the post deploy script will have to be updated, to be something like:
You will now have a mergable, referentially sound, easy to administer, super duper SQL Server project from the ashes of your .bak file situation. Congratulations, and happy scripting!