Posts filed under ‘Databases’

Porting Visual Studio .mdf Database File to SQL Server – ASP.NET

There is some confusing information out there on how to get your development database from visual studio into SQL Server so that you can run an ASP.NET website through IIS.

I’ve spent a whole evening getting this sorted and now am writing down explicit instructions on how I did it so that I (and hopefully you don’t have to spend time doing this again).


A database file in .mdf format.

IIS (Tested on IIS 5)

SQL Sever

SQL Server Management Studio Express (Free)

Download Link:

1. In your web.config file, change the connection string to the following, replacing the database name and connection string name:

2. Attach the mdf file to SQL server by right clicking the databases node, selecting attach, and pointing to the .mdf file from Visual Studio. This will be deleted later, bear with me.

3. Create a back up of that attached database by right clicking the database name, going to tasks, then back up.

4. Back up as prompted and make a note of the back up location. The back up will create a back up file in format .bak.

5. Delete the attached data base.

6. Create a NEW (as opposed to attached) database in SQL Server management studio express by right clicking databases node and selecting new database.

7. Give the new database the same name as your .mdf file, excluding the extension.

8. You are given the opportunity to assign owners. Play around a bit here. I selected all available options here, which through an error, but some owners stuck anyway, so don’t worry too much here ( a security expert may say other wise! ). Roles are added via queries in a minute so this might not even be necessary.

9. Back in the tree view, click the new database name node, go to tasks again, and select restore.

10. Point to the recently created back up file and restore.

11. Now for some trickery pokery. We need to configure security for SQL server. In SQL Server management studio express, you can execute queries against your database. Type in the following queries one at a time only replacing machineName and databaseName. This will be the name of the windows pc you are installing the application on.

EXEC sp_grantlogin ‘machineName\ASPNET’

USE databaseName

EXEC sp_addrolemember ‘dbowner’, ‘machineName\ASPNET’

USE databaseName

EXEC sp_grantlogin ‘machineName\ASPNET’

That’s it. With some luck you should be good to go here!

March 25, 2008 at 2:16 am Leave a comment


June 2017
« May    

Posts by Month

Posts by Category