Generate SQL script to Seed initial data with Entity Framework v6.1 Code First

deployment ef-code-first entity-framework-6 sql-server

Question

When deploying an application that makes use of Entity Framework v6.1 Code First I use Update-Database -Script to obtain a SQL statement I can execute against my database. How can I generate a similar script that will Seed my database with the initial data? My Production server does not have Visual Studio installed nor do I want it installed there. I would like to be able to hand the DBA a script for creating the schema (which I have as mentioned above) and one for seeding the initial data.

1
1
3/8/2017 5:12:46 PM

Accepted Answer

Assuming you have a database that is already seeded, there is an export capability (data only) in SSMS that allows you to create a script of seed data. There is a good article here. (https://keyholesoftware.com/2016/12/12/creating-a-sql-database-project-for-isolated-development/)

The premise is to use a pre-seeded database as a model. This is a re-hash of the keyholesoftware information in the link above along with a couple of comments I find personally useful.

  • Select the initial seeded database and right click | Tasks | Generate Scripts
  • Select Tables from objects
  • Use a single file for each table (provides more control)
  • Choose a directory (sandbox) where you keep your seed data (you should create a folder in your visual studio project for this location. (If you want the files kept in a virgin state, choose a sandbox for your location and then move them to this folder you create when you finish this part of the export.)
  • In the advanced section (advanced button) change the "types of data scrpt" to data only
  • Script "Use Database" to false (you're still in the advanced section do this to export to your files) and finish with this part, close the advanced portion, and close the wizard.

Hand these off to your DBA - on the other hand, assuming you have Visual Studio:

  • Back in VS, (I'm using VS2017), show all files (it is an icon in the solution explorer off to the right)
  • Include your exported tables (from the sandbox you created just now). You'll need to either set the properties of each from "build" to "none" (its the first choice in the property), or go close your project and edit the csproj file for the project to change the way it handles the included files. I use alt-Enter to open each property and set the build to none. (If it happens to you, the ERROR you'll see is "SQL71006)
  • If you have the postdeploy folder in your project, right click that now (or create it now and copy your files to this folder from your "sandbox").
  • Right click the postdeploy folder and add script | "post deployment script"
  • open the script in VS. select the "sqlcmd" mode. Caveat here - make sure your network allows sqlcmd to run - some restrict its capability - check with your sysadmin if you aren't sure what your production environment holds in store for restrictions.
  • add commands for the tables you wish to use the seed data. You may need to use SSMS to script a drop and create on your tables so that you can get around the constraints that may prevent you from running your table scripts. I started with the users - I immediately had to drop the constraints from that table in order to import. I added them drop constraints before running the post-deployment script and then re-create the constraints at the end of the script.
  • Clean and Rebuild your database project
  • Publish your project - it should create your database and add the seed data (open publish - connect to the database you are publishing to - save the profile - use the default location - click publish.
  • The process should be able to repeat itself. The first few times I deleted and recreated the database, but re-publishing over the current seems to work just fine.

NOTE - if you get an SQL71006 error, see jamie thompson's answer here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/47d4124b-82cd-48f9-8ba7-c2ae4c73cbcf/sql71006-why-am-i-getting-this-error?forum=ssdt&prof=required

1
10/29/2019 2:57:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow