nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

All comments

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.

Jeremy_a3iwv5

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.

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

ms-sql