MS SQL Backup and Restore Procedure

[David Shapiro]

I am looking for a clear description of the method used to backup production that is in MS SQL and restore to a test environment that is also using MS SQL.  

I used SQL Developer and its wizard and made sure collation was right in source and destination, but something is not right about the tables (e.g., complains about null values not allowed for various tables, and then we have some weird Cannot connect to server error when we try to edit and then update a doc.

[Davin Studer]

This is the process I use. It is based on the documentation links below.

• Make sure you have the same version of Confluence installed on the test instance as you have in production.
• Stop Test Confluence Service
• Drop Test Confluence Database
• Backup the Production Confluence Database
• Restore the Production Confluence Database to Test
• Make sure the Confluence SQL login maps up to the Confluence database as a db_owner
  • FYI: There are ways to copy the SQL login from one SQL server to another so that the SIDs are the same between the servers and then you won't have to worry about remapping the login every time you restore the production database to test.
• Delete the contents of the confluence_home directory on the test system except the confluence.cfg.xml file. On windows this is the default location ... C:\Program Files\Atlassian\Application Data\Confluence.
• Zip the contents of the production confluence_home folder except these items (logs folder, temp folder, and confluence.cfg.xml)
• Unzip the production zip file into the test confluence home folder.
• Create a temp and logs folder (as you did not include these in the zip file) in the test confluence home directory.
• In the test home directory delete everything in the index folder except the edge and plugin folders (if those exist). Everything else in the index folder can be deleted as you will be doing a full index rebuild.
• Restart the Confluence service
• Once you have the test system up make sure you update these items
  • Server Base Url (Confluence Admin -> General Configuration)
  • SMTP settings (Confluence Admin -> Configuration -> Mail Servers)
  • Application links if you have them (Confluence Admin -> Administration -> Application Links)
  • Color Scheme I usually make the top bar a different color in test than production (Confluence Administration → Color Scheme)
• Perform a content re-index (Confluence Admin -> Administration -> Content Indexing).

https://confluence.atlassian.com/confkb/how-to-rebuild-the-content-indexes-from-scratch-on-confluence-server-110035351.html

https://confluence.atlassian.com/doc/confluence-home-and-other-important-directories-590259707.html

[David Shapiro]

Thank you, 

That is a nice breakdown of what to do.  The details on bullets for Backup production and restore are kind of what I am needing.  I do not have access to the MS SQL Server as an admin.  I can use wizard in SQL Developer as long as I do not delete the users and just the tables associated with Confluence on test.

The steps I did

[David Shapiro]

For table drop in test:

I used SSMS Generate script... option.  You essentially click on the database, right click, select tasks, and then Generate Scripts....  On the Choose Objects screen, click the Select specific database objects bullet item, and select the Tables checkbox.  On the Set Scripting Options screen, select Save to new query window bullet, and click Advanced button.  For the option Script DROP and CREATE, click the second column that has Script CREATE by default, and instead select Script DROP.  This code was placed in the following Code Block.

That drops all but the users.

I see backup/restore described here:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver15

I am looking for somebody who actually did a backup/restore that can share exactly what they did.

[Davin Studer]

Ok, if you have access to SSMS you can right click the production data base and go to Tasks -> Backup. On the backup screen make sure you check the "Copy-only backup" checkbox or you will invalidate your current backup chain and any diff backups will now be off this full backup. A full Copy-only backup will do an ad hoc backup that will not affect the current backup chain. Once you have the backup, on your test server (double and triple check before you do this) right click the Confluence database and choose Delete. Make sure Confluence is not running while you do this or you might have open connections that will prevent the delete. There is a check box on the delete dialog to say close all active connection and I would go ahead and check that, but it's still just a good idea to also have the service that would be writing to the database stopped before deleting the database. After the database has been deleted right click on the Databases folder in SSMS and click Restore Database. Point the dialog to your backup of the prod database and perform the restore.

After that has been done you will want to expand the test database and expand security and expand users. Delete the confluence sql user. I don't know what you called with when you created it. Hopefully it is obvious. Then collapse the database. Then under the server folders expand Security and then expand Logins. Find your Confluence user there and right click on it and select properties. In the left nav select User Mapping. Click the checkbox next to your Confluence database. With that row highlighted in the panel below the databases check the db_owner role. What this does is map your confluence user login to the Confluence database as a db_owner.

[David Shapiro]

This is great info too, especially the bit about Copy-only backup.  The thing is if I delete the database, I will lose my access since I am not the dba.  What happens if I instead use the wizard to remove all the tables and then do a restore?  I guess I need a way to work under this limitation.

[Davin Studer]

Well, hobbled as you are I don't know of a great solution for you. Since you don't really have much access to the database I would then say maybe get your DBA in on the process. If that is not possible you could also try using the Backup and Restore functionality of Confluence. It is under Confluence Administration -> Administration -> Backup and Restore. It will create an XML file of your current system that you can use to restore to another system. If you have a really large system this approach probably will not work very well, but if it is smallish it may work ok for you.

[David Shapiro]

If the xml backup/restore still worked for me I would lovingly use it.  Sadly, at a certain size, it finally just does not work (timeouts, fails, etc).  Months of attempts with Atlassian and them also pointing at their docs where it states xml backup/restore is fine for just small environments due to timeout issues, I had to abandon that.  

The method I use seems like it should work, but it just does not.  I use SSMS to DROP everything other than the users to allow me to keep access to the database.  I re-install the Confluence on test server to match the version on production, including the add-ons.  I then use SSMS wizard to export/import the data to the test database.  My confluence.xml.cfg file I had to change the version to match what is in production.

I have to change the server id in the bandana table back the test server's id after.  

The server comes up, but there are various tables is complains about will not take null values, that have me going to SSMS for those tables and in design mode changing under identity specifications Is Identity is Yes instead of No.  I am not sure why this is happening. 

Everything seems wonderful, but if I try to edit a document now and click update without unchecking Notify Watchers, it fails with a server connection error.  

It is just one bloody annoying mess since I cannot use the xml backup/restore.

[David Shapiro]

There was also another requirement, which was to switch out references to the production server in the CONTENT table to reference the test server instead.  Otherwise, if you look in your browser in developer mode for example, you would see the page content would have some things from the production confluence server inter-mixed with the test server (e.g., links to docs, css, etc).  I suspect that xml maybe does not address that other, but I have not checked.  If it does, that is great, but obviously using a 3rd party backup/restore process like SMMS is not going to address this.  If you did not do this fix, then when prod is down, the test server would also not show anything that needs production up to see it.  You really do not want your test stuff trying to get stuff from prod regardless.

[David Shapiro]

I misspoke, it is the table called BODYCONTENT in thr column called BODY.

[Davin Studer]

Yeah, really the best way to do a refresh of a test system is via a database backup/restore. That is the method that Atlassian recommends. Trying to wipe tables and then do inserts it just rife with so many possible failures. I would recommend doing it via a database backup and restore and it sound like you will need to get the DBA involved, but that is going to be the least error prone method and in the long run will probably be the fastest.

As for repointing stuff on the test server there is an app called Keysight Admin Tools for Confluence that can do a find and replace over a space and that could help. Another option would be to use the rest api for this.

[Jeremy]

Hi there what version of SQL Server are you using and what type of backup options. As there is a few.

Have you tried to SQL Profiler on the backups as this give you an idea what is happening with the backups.

If you have any screen shot will help as well.

[David Shapiro]

I am not familiar with SQL Profiler.  When you say backups, are you talking about the xml back?  Those backups fail to get generated.  

[David Shapiro]

Note the version is 13.0.5426.

I am not the actual dba too, and the only access I have is the actual database used by Confluence.  I can drop all the tables and do export/imports with the wizard.

[David Shapiro]

By the way, the size is such that xml backup/restore does not even work anymore (timeouts happen).  I attempted to work with Atlassian non-premier support on this for quite some time and they just said use native backup/restore used by database.