Learn DNN / DotNetNuke

You need to Register for free and Login to post a message in the forum.

Forum

Forums
Search Forum
Advanced SearchTopicsPosts

Forums > DotNetNuke® > DotNetNuke® Questions

Migrate from SQL Server to SQL Server Express

Last Post 12/27/2011 9:52 PM by TommyOnDNN. 4 Replies.

Sort:

Check this box to subscribe to this topic.	Prev Next
You are not authorized to post a reply.

Author

Messages

TommyOnDNN

Nuke Ace
Posts:41

12/25/2011 3:48 AM
I used to develop DNN straight from the server, going to http://www.sitedomain.com and manipulating CSS files through remote desktop. My server runs Windows Server 2008R2 with SQL Server 2008 Enterprise. Sometimes development is very slow due to load times and currently I want to try a module that only allows trial usage from localhost domain, so I want to migrate the site from the server to my laptop, which has SQL Server Express 2008 R2 installed. I backed up the DNN files and created a SQL file backup (*.bak) from the database using SQL Server Management Studio. I'm perfectly able to restore the database backup to my local SQL Server Express 2008 R2 installation, however I can't figure out how to format the connection string properly, or at least can't get the database to be used by the local site. On the server the connection strings in the web.config would be: I've tried: But this doesn't work. Anybody any suggestions?

Web Services

Nuke Master
Posts:322

12/26/2011 2:12 PM
Can you access management studio on the server its self? If so try this. If you were using integrated security locally, then you will probably need to create that user on the server. I would not recommend using integrated security on the live server. If you can access sql management studio, go to security under the server instance and users. From there, right click and add a new sql user. In there, change the radio to sql server authentication. Put in a user name and password. MAKE SURE you uncheck user must change password at next login, otherwise your connection won't work. Also, uncheck enforce password expiration, otherwise, some day your site will stop working and it will difficult to figure out that the password expired. While you're still in that new user window, click User Mapping on the left. Find the database in the list, check it, and then below that check db owner. You should now be able to specify that userid and password in your conn string. One thing to note, even if it looks a user has permissions to the database, if you did a restore, then the user name there doesn't exist on the server or isn't the same even if you see it under users on the server. What I mean is, let's say you had the user someUser created on your local. You restored the db to the live and had the user someUser created on the live server. If you expand security logins under the actual DB you would see the user someUser. However, this user is the user from your first server, and technically doesn't exist on the second server, even though the username's are the same. MS SQL uses unique id's to identify those user names, some from a GUI point of view, you would think the user someUser has access to the database, but in reality it's not the same user. That's the user that it was running on under your sql express instance. Feel free to contact me if you have anymore questions, I have had to move over 200 dnn installs to a new server, along with about any other sort of migration you could imagine.

TommyOnDNN

Nuke Ace
Posts:41

12/27/2011 4:42 AM
Thanks for reply web services, unfortunately I can't the database restored properly locally. I can't get it to connect to the SQLExpress Database. I have complete access (RDP with domain admin account) to both the production server and local instance (ofcourse). Connectionstring on production servers: <add name="SiteSqlServer" connectionString="Data Source=BNOPRDWIN2008R2,8639;Initial Catalog=DATABASEA;User ID=USERNAMEA;Password=PASSWORDA" providerName="System.Data.SqlClient" /> <add key="SiteSqlServer" value="Data Source=BNOPRDWIN2008R2,8639;Initial Catalog=DATABASEA;User ID=USERNAMEA;Password=PASSWORDA" /> On the local machine I restored a backup of the database using SQL Server Management Studio. This all goes well. Due to your mention about the username, I created a new SQL User USERNAMEB, gave it the Sysadmin server role. Added USERNAMEB as dbo to the restored database named DATABASEB. Been trying variations in the connectionstring on the local machine, but cannot get DNN to successfully connect to the database. Examples of connectionstrings I've been trying: <add name="SiteSqlServer" connectionString="Data Source=COMPUTERNAME\SQLEXPRESS;Initial Catalog=DATABASEB;User ID=USERNAMEB;Password=PASSWORDB" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Data Source=(local);Initial Catalog=DATABASEB;User ID=USERNAMEB;Password=PASSWORDB" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Initial Catalog=DATABASEB;User ID=USERNAMEB;Password=PASSWORDB" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Server=COMPUTERNAME\SQLEXPRESS;Database=DATABASEB;uid=USERNAMEB;pwd=PASSWORDB" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Server=(local);Database=DATABASEB;uid=USERNAMEB;pwd=PASSWORDB" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Server=.\SQLExpress;Database=DATABASEB;uid=USERNAMEB;pwd=PASSWORDB" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Data Source=COMPUTERNAME\SQLEXPRESS;Initial Catalog=DATABASEB;User ID=USERNAMEB;Password=PASSWORDB;" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Data Source=(local);Initial Catalog=DATABASEB;User ID=USERNAMEB;Password=PASSWORDB;" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;Initial Catalog=DATABASEB;User ID=USERNAMEB;Password=PASSWORDB;" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Server=COMPUTERNAME\SQLEXPRESS;Database=DATABASEB;uid=USERNAMEB;pwd=PASSWORDB;" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Server=(local);Database=DATABASEB;uid=USERNAMEB;pwd=PASSWORDB;" providerName="System.Data.SqlClient" /> <add name="SiteSqlServer" connectionString="Server=.\SQLExpress;Database=DATABASEB;uid=USERNAMEB;pwd=PASSWORDB;" providerName="System.Data.SqlClient" /> Any help would be greatly appreciated.

Joseph Craig
DNN MVP
Posts:11667

12/27/2011 7:11 AM
For now, stick with SQL Server Management Studio Express and see if you can connect to the database with it. Until you can do that, there's no point in fiddling with connection strings inside of DotNetNule. Assuming you can connect using SQL Server Management Studio Express, what are the server, database, user and password you used successfully? (And, if you've created a new DB user, make sure that use has dbo privileges on the DotNetNuke database.)

Joe Craig, Patapsco Research Group Complete DNN Support

TommyOnDNN

Nuke Ace
Posts:41

12/27/2011 9:52 PM
I just found the error, thanks to Joseph's tip. I was perfectly able to connect to the database engine using my Windows account, however didn't try to connect using SQL Authentication. After I tried this, I learned that the SQL User account wasn't able to connect to the database engine and the link provided in the error message when trying to login solved the issue (SQL Express on my local machine was configured to operate in Windows Authentication mode only - http://www.microsoft.com/products/e...ID=18456). Thanks for the advice guys!

You are not authorized to post a reply.

Forums > DotNetNuke® > DotNetNuke® Questions

Latest Forum Posts

Can you Run Xcode in Linux? by Aman Singh

Can you Run Xcode in Linux?

Can you Run Xcode in Linux? by Aman Singh

Can you Run Xcode in Linux?

Billed for subscription that was canceled by Ryan

I was just billed for a subscription that was canc eled / inactivetive how do I get a refund for th

Meta Title and Meta Description are not showing properly by DNN User

Hi, On my site the meta title and meta descriptions are not showing properly. It's showing the m

Search is not functioning properly by DNN User

Site: https://www.prv-engineering.co.uk/search If I search any item only the 1st option under sea

Search is not functioning properly by DNN User

Site: https://www.prv-engineering.co.uk/search If I search any item only the 1st option under sea

DNN Platform import site optio by Melanie Weaver

Is it possible to import site template in DNN Platform 8.0? We have internal Evoq Content 8.1 tha

Events Module for DNN V5? by Tugboat

Would anyone have a download link for the version 5.0.3 Events Module? Thanks!

"Ghost" TabID Number? by Tugboat

Hi! I have a portal instance with multiple child portals and on one of the child portals, there i

RE: DNN Load Testing by ayman sharkawy

Hi. Have you already implemented a site using the DNN . And how the performance of the site and its

Publish All Pages by NSUOK

I'm using DNN Evoq Content Basic 8.2.0. When I make the HTML Pro module display on all pages, I h

Simpler profile needed in 9.2.2 by Donald

We are upgrading a DNN 4.8.4 site to DNN 9.2.2. On 4.8.4, the top bar shows the user’s name. If yo

How to link from dnnmodal popup to web site page? by Donald

Hello all. I am using a dnnmodal.show popup. The popup works correctly, showing the content that I

RE: Document Collaboration by Nick Davern

That feature would be super beneficial for our needs as well! Does anyone have an update as to if th

RE: DNN 9.2, how to set default theme? by Andy Stephenson DNN Creative

you do that under "Manage/Themes". Note the highlight blue border around the default container and d

DNN 9.2, how to set default theme? by Donald

Please disregard, found Manage Themes I cannot find how to set a default portal theme in DNN 9.2

RE: Looking for DataSprings Dynamic Forms by Andy Stephenson DNN Creative

Your best chance would be to contact Chad Nash @ http://www.datasprings.com/

Looking for DataSprings Dynamic Forms by Jakir HM

Looking for DataSprings Dynamic Forms 3.4 for DNN 4.x 5.x for my site (https://themasters2018s.com/)

RE: Help with Cycle Image Carousel not displaying images by peter

I had the same problem with the Will Strohl Content Slider after upgrading to DNN 9.1 The above an

RE: Object reference not set to an instance of an object by Andy Stephenson DNN Creative

Check this one might be useful: http://www.dnnsoftware.com/answers/im-tryin-to-move-my-site-to-pr

Used
By

Testimonials

| The SoloCoder Podcast | Google | Privacy Statement | Terms Of Use