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.
This is the process I use. It is based on the documentation links below.
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
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:
I am looking for somebody who actually did a backup/restore that can share exactly what they did.
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.
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.
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.
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.
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.
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.
- Create your own custom emoji 🔥 - "Shake for Feedback" on mobile 📱 - An endless supply of GIFs via GIPHY 🤩 Is there anything quite as nice as a pleasant surprise? Comment below with what...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events