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.
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