I am working on a large DNN project for a university where they did the original install of DNN and now I am building out content. Actually I am a sub on this project. So I have several weeks worth of content done and the IT department at the university was not sure how to setup backups for the SQL Server Express database. They gave me remote desktop access to the server and I had planned to just install a script that I use on my servers to do backups of my databases. This was my first access to the server.

So I opened up SQL Server Management Studio and did not see any databases, other than the System dbs. Then I looked at the web.config to see what the db name was in the connection string. This is what I saw.

<add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />

This was a little foreign to me as all of my DNN installs use a standard SQL connection string with SQL Server authentication. My assumption is that this is a windows authentication setup.

I did some research and saw a post about not being able to see databases setup this way in SQL Server Management Studio. There was a work around mentioned, but I could not get it to work. I spoke with the IT people that setup the DNN instance. This was their first DNN install a couple of months ago. All they could offer was that they followed the instructions. I noticed that the database name was database and that it was located in the DNN app_data folder on another drive (F:). Is this where DNN puts the database? I use DotNetPanel for all my DNN installs now and it has an auto installer. So I am not that familiar with how the manual DNN install works now.

So here was my thought. I copied the database.mdf and database.ldf files over to another SQL Server express I have on another server and attached it there. For some reason I was not able to get it to attach correctly on the university's server. From there I created a database backup (.bak) file.

Then on the university server I created a new database with a SQL Server authenticated user. I was able to force a RESTORE from the .bak file to this new one and now I have a DNN database that is correctly attached to the university SQL Server and has a SQL Server authenticated user login.

My intention was to then change the connection string to more of a standard SQL Server authenticated string like

<add name="SiteSqlServer" connectionString="Server=iarsvr04\SQLExpress;Database=databasename;uid=userid;pwd=password;" providerName="System.Data.SqlClient" />

The guys at PowerDNN helped me out on this.

I agree that the dynamic attachment introduces lots of limitations. I got into this after the fact and was not provided access to the server until after putting in several weeks of data through the DNN interface. Then they needed some help on the database backups and that is when I found this was their very first DNN install and that they had used the non-preferred method during the setup.

Mitchel you were right in that the instance needed to be named correctly. Originally I had tried a full name like iarserver/SQLExpress  but it would not work. So I just tried (local) and that did not work.

There was also an issue with the fact that I did not assign the user to the schemas. I think I read somewhere that that was not required. Ultimately they set the connection string server to (local)/SQLExpress, but I think my original setup (iarserver/SQLExpress ) would have worked if I had had the schema thing setup correctly.

Anyway, we are cooking with gas now and I have tons of content to move over to have the site almost ready by 31 Dec. A great way to spend Christmas break.

Cliff