Changing database connection string has no effect

We are trying to restore a Sitefinity 6.2 instance manually, Site Sync is not an option in this situation and so we are resorting to manually zipping up and copying the project source code, files and database across to the target server. Then, restoring the backed up database on the target with a new name, backing up and overwriting the contents of our IIS directory with the copied project code and files, and finally changing the connection string in ~/App_Data/Sitefinity/DataConfig.config to point to the new database instance.

Whilst cumbersome, especially in a load balanced environment (and I mean actual load balanced servers, not Sitefinity in-app "Load balancing"), this should work. The problem we have is that Sitefinity seems to somehow cache the data, meaning that even though I can see the new connection string in the config file, when I navigate through the UI to Administration > Settings > Advanced > Data > Connection strings > Sitefinity.... I still see the old data in the website. I have tried, to no avail, recycling the App Pool, and even restarting the website in IIS.

So, thinking that perhaps Telerik intended us to use the UI rather than edit the DataConfig.config file directly, I changed the values in the UI and saved them. Checking the config file I can see the new values have been applied to it, but still no effect. I then deleted the Load Balancing WebServerUrl's in case that was somehow affecting it. Still no effect. To test a theory (that our DBA had possibly restored the wrong database), I actually changed the config to point to a non existent database. Still no effect - i.e. I am still seeing old data in the website.

In desperation, I deleted the Sitefinity data setting (containing the connection string) from the UI altogether and then the actual DataConfig.config file itself from the directory. Unbelievably this still had no effect! With seemingly no data connection configured, Sitefinity is still serving up data from a database that doesn't exist.

Stumped, and with a meeting to go to, I left everything in the above state and returned an hour later. On my return, after refreshing the web page again, I was relieved to see an error saying that no connection string could be found. So I copied in the DataConfig.config which I had saved out to a temporary location, complete with new connection string pointing to the restored database.

Satisfied that everything is now setup correctly, I refreshed the web page, only to be presented with...
"CREATE DATABASE permission denied in database 'master'"!! - i.e. Sitefinity cannot find the database and is trying to create a new one...

Obviously I don't want to create a new database at this point (I guess with different permissions I would get forwarded to the /Sitefinity/Startup page, but this would be equally unhelpful) and so now I'm a bit lost on how to proceed with this....

Is Sitefinity caching the data? Or the data connection string? Is there some way to force a refresh of this?

Is there a better / easier way to move to a new database?

Any help greatly appreciated

I have solved this issue.

I'm still not sure if Sitefinity caches the data or the connection string. This could explain the odd behavior we experienced whereby we could still see the data even after completely removing the connection string (which I guess is a good thing, if you ever deleted the LIVE connection by mistake, you wouldn't see an immediate loss of the website), but the issue we had turned out to be a permissions problem on the database.

I'm not a DBA so I don't fully understand the issue, but I'm told an "ALTER AUTHORIZATION" command was required (I assume the user account to access the DB is different on each machine and the restored database was still setup for the previous machine's user), and this hadn't been done. In effect, the user was denied access to the database. Seems obvious now, looking back at the error.

Either way, as soon as the DBA ran this query, I immediately saw the new data.
I hope this helps somebody else if they ever face the same problem.

Hey Mic,

You can configure Sitefinity to either read/save settings from the DB or from the .config files - so in that scenario your .config files basically get 'ignored'.
But if you've kept the default setting of retrieving them from the .config files, it only reads them on application start.
Second thing is that data-caching happens, so a data change might not be instantly reflected, you can set that btw (www.telerik.com/.../302577_data-caching-settings.PNG)

In your case, judging by your description I'd say you've just ran into a 'unlucky' combination of events of IIS not properly invalidating, making .config changes and not restarting and data-caching that shows the old info.

First an app-recycle and website restart doesn't always invalidate your server side temp/cache files. 
Often stopping the website from IIS manager and hitting refresh will still simply serve it, and not till the next F5 will it recognize the site is actually down.
On rare occasions you even have to manually clear \Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files but that's all IIS/Server config related not necessarily Sitefinity.

For a fail-safe approach I'd go with the following:
Bring the site down (IIS Manager > stop) then recycle app-pool.
Check web.config that it reads configuration from .config files. (www.sitefinity.com/.../working-with-configuration-providers)
Change data.config.
Start your website in IIS Manager again.

This is a great post with good information. I have one request. Could the url's that Jochem shared be updated? They don't work now.

www.telerik.com/.../302577_data-caching-settings.PNG

www.sitefinity.com/.../working-with-configuration-providers

Regards