Posts
22
Comments
696
Trackbacks
9
How to (not) restore SQL-driven ASP.NET websites

After having really nothing important to report on for quite a while I wanted to share some experiences and gotchas I ran into while restoring this blog.

Basically what had happened was that my server (and the rest of the stuff from my old house) was moved to a new location, and at the same time I decided to perform a hardware refresh (I really wouldn’t call it upgrade since I replaced the existing processor with a low power Celeron and an energy saving power supply) and topped it off by switching from the old Windows Vista 32bit system to a fresh Windows 7 64bit install. Since I was also using a new hard drive for the OS install, I still had all the old data, but I didn’t bother taking any database backups or saving any files before I wiped the machine out.

On the software side the following things changed:

  • I went from Windows Vista 32bit to Windows 7 64bit
  • That also meant going from IIS 7 to IIS 7.5 which comes with Windows 7
  • Instead of SQL Server 2005 32bit I installed SQL Server 2008 SP1 64bit

And these are the things I had to do to get my blog and other ASP.NET web applications running again:

  1. After installing Windows 7, I added IIS7.5 to it and then installed SQL Server 2008 SP1 64bit. So far so good!
  2. Next I copied over all the ASP.NET web applications from the old harddrive wwwroot folder into the Windows 7 wwwroot folder. You might want to make sure you give the files the right permissions and take over ownership as needed, otherwise the user accessing the web application might have issues reading the files.
  3. Getting my old SQL Server 2005 databases into SQL 2008 was much easier than I thought, especially since I didn’t take any backups but only had the original MDF files from the old Data folder. I copied all the files from the old folder (depending on the name of your SQL instance the folder path should be something like this: Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) into the same path on the SQL 2008 installation. Then I fired up SQL Server Management Studio and used the Attach menu option (see screenshot below) to load the MDF files and import everything into SQL 2008. It even took care of automatically upgrading the databases from SQL 2005 to SQL 2008!

Microsoft SQL Server 2008 Attach Database

Now for the complicated part: Since Windows 7 comes with IIS 7.5 there were some changes Microsoft had made that I was not aware of. They have since been widely published and talked about in other blogs such as here and here. The main change that affected me is the fact that the default Application Pool Identity in IIS 7.5 changed from NetworkService to AppPoolIdentity. That meant my database connections were not working anymore, because the authenticating user was not a member of the database users. To get it working again I had to do the following:

  • First I removed the old NetworkService account from the database users, since I did not want to have old entries lingering around. The account name is NT AUTHORITY\NETWORK SERVICE.
  • Then I had to do some reading to find out what the actual username of the AppPoolIdentity is. Finally I figured it out: It is IIS AppPool\<app pool name>.
  • After adding the app pool users to their respective databases, everything immediately started working again!

In the end I learned a couple of neat new things about IIS 7.5 and how to restore web applications with databases without much preparation :-)

posted on Wednesday, October 21, 2009 2:53 PM Print
Comments
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
jocuri fotbal
1/15/2011 2:27 PM
That meant my database connections were not working anymore, because the authenticating user was not a member of the database users. To get it working again I had to do the following:
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
apartments bangalore
6/23/2011 2:52 AM
great share, thanks
Apartments in bangalore
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
aluminium extrusions manufacture
6/29/2011 11:57 PM
I upgraded server recently. Thanks for the post!!
Gravatar
# SJR Group
Ash Gupotah
6/30/2011 11:43 PM
Very informative post. I don't think I've come across the angle you present the information in a brilliant way. Very original! Keep blogging.
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
Advokat
10/31/2011 9:02 AM
I upgraded server recently. Thanks
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
shine
12/15/2011 11:45 AM
Cover Letters For Medical AssistantIt is really nice article having nice information. I would like to appreciate writer 's nice effort. Keep this sort of posting in future as well. I will be back after some time
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
jimy04
1/1/2012 10:31 AM
The best time to get anything electronic is on Black Friday. While that fact has always held true, it is going to be especially good this year.black friday kindle fire
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
shine
1/15/2012 8:57 AM
2011 Tax Return Estimator Nice blog having nice information. I would like to appreciate writer 's nice effort of posting useful information.
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
sunshine
1/23/2012 6:33 AM
Homemade Bread Recipes Most households regard bread as a staple food item. So, every mom makes the trip to the local bakery to get the daily dose of bread.
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
world wonders
2/4/2012 5:45 AM
The seven World Wonders list of the ancient times was initially recorded approximately in the 2nd century BC. 
Gravatar
# re: How to (not) restore SQL-driven ASP.NET websites
Horror Movies
6/28/2013 3:07 AM
I want to say that Your efforts are really terrific. I always found it very difficult. your way of explaining is more convenient to understand for the readers.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 6 and 4 and type the answer here:
News
Welcome! This is the blog of a .NET software development enthusiast living in Silicon Valey, California, USA. The opinions posted here are my own and in no way represent the opinions of my employer or anybody else.