1

I have an ASP.NET MVC 3 application using an Entity Framework (4.3.1) Code First database. Now I would like to create a comprehensive zip file containing the database, the application package generated by Visual Studio 2010 and a script to deploy everything to a Windows 2008 server with IIS7 and SQL Server 2008 with a prepared (but empty) database.

I don't foresee any problems with the deployment of the application package, but I'm unsure of what approach to use in deploying the database. The target environment already has an empty database that's been assigned to me, but I've been told that dropping and creating the database is fine.

From what I've read, I can do a straightforward copy of the .mdf and .ldf files to the server and then setup my connection string to point to that specific file but this approach sort of ignores the database that has already been created (or at least named) for me. The other approach would be to use the the existing .mdf to create the database on the server with a script. My only issue here is that I would like to keep the database name assigned to me.

Improve this question
1
  • 1
    Besides @MattSavage nice answer you should also know that moving database files to remote server in most cases won't not possible if you are using shared hosting solutions. Even keeping your database name probably won't gonna happen. It's because you won't be assigned to dbcreator or even sometimes even db_owner roles due to security reasons. Commented May 25, 2012 at 20:44

1 Answer 1

Reset to default
8

I usually connect to my development database locally using SQL Management Studio and right-click the database, choose Tasks -> Generate Scripts. Then I select the entire database or just the tables I'd like to keep, click next, then click the Advanced button and make sure that I am scripting out "Schema and Data", and then generate a sql script that I can run on the production database, therefore keeping the table structure and the data that was in the dev database. Obviously, if you don't want to keep the data then just script out the Schema only. Then, point your application's connection string to the new production environment database and you're good to go.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you Matt, this is indeed what I was looking for. My only question now is how I can specify where this create script will create the new database or what database name it will get. In the created script, the first line reads USE [C:\SolutionName\ProjectName\APP_DATA\ASPNETDB.MDF], am I correct in thinking that this will look for a database in that location and overwrite/recreate it? If I want the database to have the name of my project rather than the generic ASPNETDB one, will I have to rename the mdf itself or can I do this with the Management Studio?
Foolish of me: I simply had to change the name of the attached DB to do this, and I've got everything working now. The fact that the attached DB's name was based off the path of the .mdf had me thinking I needed the .mdf itself as well for some reason. So your answer pointed me in the right direction. Note though that on my version of the SQL Management Studio there is no Advanced button, preserving data is done by setting the "Script Data" option to True.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.