How to access content/data from a SQL Server .MDF file (+ error installing on local server)

[Gary Schneider]

Hello,

We have been subject to virus attack that destroyed our server, luckily we still have the SQL Server MDF file for confluence database!

Question 1: How can we recover the spaces and articles inside this MDF file?
Also,

I tried installing Confluence again, but give me error : 

com.atlassian.util.concurrent.LazyReference$InitializationException: java.lang.IllegalStateException: Spring Application context has not been set
    at com.atlassian.util.concurrent.LazyReference.getInterruptibly(LazyReference.java:149)
caused by: java.lang.IllegalStateException: Spring Application context has not been set
    at com.atlassian.spring.container.SpringContainerContext.getComponent(SpringContainerContext.java:48)

Question 2: Can you assist on finalizing the installation process?

Please contact Mr Nabil Kemerchou: nkemerchou@hopitel.com for technical debugging 

Regards,

[Nic Brough -Adaptavist-]

My understanding of MS SQL is that you can't just plonk an MDF file in place and expect it to be a database in the new MS SQL server you've built   

You will need to recover the data from the MDF (and, if memory serves, the matching LDF file) and then connect Confluence to the recovered database.

What have you done to recreate the database from the MDF file?  Can you connect to the new database and see Confluence tables in it from a command line or database viewer?

[Gary Schneider]

As stated in my request, I tried to install Confluence again and it did fail with the message provided! 

The MDF files itself can be attached to the database, and I can see all the data, the issue is how to connect a Confluence FrontEnd to the data.

So, do you think you can help us recover the original data from the MDF file ?

[Nic Brough -Adaptavist-]

It will give that message if the database is invalid, or any one of a hundred other problems.  I focussed on the database because you started with the mention of an MDF file, which is irrelevant to Confluence - it only cares that there is a usable database (which an MDF file alone is not)

So, just to re-iterate - has your MDF and LDF files been converted back into a fully working database?  Not "attached", but converted into a database?

[Gary Schneider]

Let me try to clearly it : to you question " has your MDF and LDF files been converted back into a fully working database? " the answer is no, and that exactly the purpose of the support request!

The MDF is the database (with the ldf which we also have)! not sure why you said it is not!? and attaching the MDF to the SQL Instance will give a fully functional DB from SQL server perspective! but again I am looking for a procedure to read the data from Confluence FrontEnd!

I'm having the impression that i'm repeating the same thing again, maybe having a quick call will clarify it for you

[Nic Brough -Adaptavist-]

From my memory, an MDF file requires some manipulation in order to restore it as a working database, and that's outside the functions Confluence does, all it needs is a working database connection.

That's completely outside the remit of Atlassian support, let alone the community here (we're not support by the way), it's not an Atlassian thing.

Anyway, assuming that you have done that manipulation and you have a valid working database, the next thing to look at is the rest of the Confluence log file.  "Spring context cannot be set" is a generic error which will have been caused by one or many other errors.  

You'll need to read through those to see what it is.

I'd also want to check what you restored from the Confluence home directory, if anything?  Have you done a fresh installation, got a copy of the home directory, or is this the existing one (and you only lost the database)?

[Gary Schneider]

For your comment: "From my memory, an MDF file requires some manipulation in order to restore it as a working database", its not the case, in fact once you have an MDF file, you can attach it by just selecting the MDF file and SQL server will give you a fully functional database, so i really did not do any manipulation!

The only thing we had from that server was the MDF file (no log or anything else), so again, i tried by re-installing Confluence from the latest setup file.

My plan by creating this support ticket is to fix the new installation and then I was hoping to find some kind of migration script from the recovered MDF file (i.e. the original database) to the newly installed DB. I did similar thing in a different context with the help of SQL Server Profiler to identify the right set of tables to update.

So, the goal is to re-install Confluence without errors (which i was able to do couple of years ago with the version of that time) not sure what prevents todays installation from proceeding!? then try to migrate the data with an exiting script!

[Nic Brough -Adaptavist-]

Ah, ok, that changes it a bit. 

I was assuming you were pointing your system directly at the recovered database from the MDF file, but it now sounds like you're trying to work with a new empty database.  Which is what I would do - get new install working then flip the database.

So, starting an install from scratch (let's skip the database stuff for now, that's not a problem yet), what did you do there?  You've already said "new empty database", but is it safe to assume you installed Confluence clean, no copy of home directories or anything else?

If so, then how far through the install are you getting this error?

[Gary Schneider]

I will just clean the erroneous installation and re-install again, it should work! I think i can come up with the migration script from the old to the new DB.

Thanks for your support, this ticket should be closed.

[Nic Brough -Adaptavist-]

Ok, great!  But there is no "migration script" needed.

Once you have an empty working Confluence (i.e. you can log into it as an admin and wander around), you can:

• Stop the new Confluence
• Amend <confluence home>/confluence.cfg.xml - look for the database connection string, changing it from <name of new install database> to <name of recovered database>
• Copy (or move) the old <confluence home>/attachments directory into the new <confluence home>
• Start Confluence
• Go to Admin -> indexing and kick off a full re-index

Once the indexing is complete, you should have a fully restored Confluence!

[Gary Schneider]

Yes that a good tip, I also was able to recover all the articles with the following SQL script,!

The issues is that I couldn't find the images related to the articles! I assume that these attachments are saved on disk? if that's the case then we lost them as the old attachment folder was lost due to the virus !

SELECT c.[CONTENTID]
      ,[CONTENTTYPE]
      ,[TITLE]
      ,[VERSION]
     , b.BODY
     ,c.[LASTMODDATE]
     ,[SPACENAME]
  FROM [Confluence2].[dbo].[CONTENT] c
  inner join [dbo].[BODYCONTENT] b on b.CONTENTID = c.CONTENTID
  inner join [dbo].[SPACES] s on s.SPACEID = c.SPACEID  
  order by SPACENAME, [TITLE], VERSION, c.[LASTMODDATE]