QualityCenterBackupandRecoveryManagement.doc

Quality Center Backup and Recovery Management Guideline

File Name
Quality Center Backup and Recovery Guideline
Introduction

Company
StarCite
Author
Sincky
Date
8/11/2009
Status
Draft

Date
Modifier
Reason
Chapter
Description
8/11/2009
Sincky

First Version

Section I Quality Center Backup Strategy 3
1. Quality Center Backup Introduction 3
2. Quality Center Repository Backup 3
3. Quality Center Database Backup 4
Section II Quality Center Recovery Strategy 5
4. QC Database Recovery 5
4.1 former Database Server Recovery 5
4.2 New Database Server Recovery 5
4.3 Database Server Recovery Process 5
4.3.1 Recovery database 5
4.3.2 About [TD] login 5
5. QC Repository Recovery 6
5.1 Formal QC Server Recovery 6
5.2 Informal QC Server Recovery 6
Section III Quality Center Upgrade Strategy 8
6. Quality Center Upgrade 8
6.1 Formal QC Server Upgrade 8
5.2 Informal QC Server Upgrade 8

Section I Quality Center Backup Strategy
1. Quality Center Backup Introduction
Quality Center Server IP: 10.200.30.117
Quality Center Version: 9.2 Enterprise
Quality Center backup includes 2 sections:
Ø QC Repository backup
QC Repository is a path to save the entire of resource and data about QC site administration and projects. One of QC site administration parameter “BASE_REPOSITORY_PATH” means the path of it. StarCite QC Repository path is:
C:\Program Files (x86)\Mercury\Quality Center\repository
There are 2 folders in this path:
· QC: it is Projects repository, we need to backup the necessary projects data
· SA: it is Site Administration repository, we need to backup the whole of it
Ø QC Database backup
StarCite uses Microsoft SQL Server2005 as QC database server; it is also installed on 10.200.30.117.
We need to backup all of the SQL Server database data for QC server.
2. Quality Center Repository Backup
QC Repository backup adopts a scheduled batch script to backup automatically.
1. The batch script is in C:\Program Files (x86)\Mercury\Quality Center\repository\ AddQCFiles.bat; it will zip the backup contents by 7zip tool and save as another disk
2. The backup content includes 3 parts, they are [repository\sa] folder and [repository\qc\STARCITE_3] folder and [repository\qc\dbcon.txt] file
3. This batch script has been added to Windows schedule task, by default it will be run at 12:00 AM every Saturday automatically
4. The backup destination folder is D:\Backups\QCRepositoryBak

3. Quality Center Database Backup
QC Database backup adopts SQL Server Maintenance Plan to backup the necessary databases automatically on 10.200.30.117. Notes, [Master] database is ignored here.
1. The Maintenance Plan is saved in [localhost\Management\Maintenance Plans] folder in Microsoft SQL Server Management Studio
[QCDBFullBackupPlan]
[QCDBLogBackupPlan]
2. QCDBFullBackupPlan will full backup database: [HP_DASHBOARD_920], [qcsiteadmin_db], [starcite_smallmeetingsolutions], [training_testtesttest_db], [QualityCenter_Demo_db], [training_qc_rally_test_db]
3. QCDBFullBackupPlan will transaction log backup database: [HP_DASHBOARD_920], [qcsiteadmin_db], [starcite_smallmeetingsolutions], [training_testtesttest_db], [QualityCenter_Demo_db], [training_qc_rally_test_db]
4. The backup destination folder is D:\Backups\QCDBBak by separated database name folder
5. Database strategy is:
Full backup – executes at 2:00 PM every Saturday, and deletes backup file 2 weeks ago automatically
Transaction Log backup – executes it every 4 hours every day, and deletes backup file 2 weeks ago automatically
Section II Quality Center Recovery Strategy
4. QC Database Recovery
4.1 former Database Server Recovery
If the former Database Server is broken:
1. uninstall the former SQL Server software and all of QC databases
2. reinstall SQL Server software in the former Database server machine; note the SQL Sever version is same as the former one
3. restore all of QC databases according to the next steps
Nothing if the former Database Server is available.
4.2 New Database Server Recovery
If want to change SQL Server to another machine:
1. install SQL Server software on the new machine; note the SQL Sever version is same as the former one
2. restore all of QC databases according to the next steps
4.3 Database Server Recovery Process
4.3.1 Recovery database
1. get all of database backup files from D:\Backups\QCDBBak to QC database server, including [qcsiteadmin_db] and other project databases
2. restore these databases from the latest full backup files by [restore with nonrecovery] mode with the same database name with database’s original name
3. restore the necessary log transaction files to these databases by [restore with recovery] mode
4. verify if there is a [td] user and [td] schema in every restored database
4.3.2 About [TD] login
1. if SQL Sever that QC will connect to is a new installation, create [td] login account in this SQL Sever
2. set [default schema] to [td] for all of the restored database in [td] login properties setting
3. set [db_ddladmin] and [public] role membership to all of the restored database in [td] login properties setting
4. if popup error when set role membership, execute SQL to modify SQL Sever parameter:
EXEC sp_change_users_login 'update_one', ‘td’, ‘td’;

5. QC Repository Recovery
After restored all of the necessary databases to SQL Sever, including [qcsiteadmin_db] and other project databases, we can restore QC repository. If QC server is broken, it is necessary to reinstall QC firstly.
5.1 Formal QC Server Recovery
1. uninstall QC software in QC server
2. invoke QC setup program and install it into the same folder with the original QC path
3. if [qcsiteadmin_db] is restored or existing in QC Database server, choose [upgrade existing qcsiteadmin_db schema] when install QC software
4. use [sa] login to connect SQL Server when setup QC software
5. set the correct [td] password to [qcsiteadmin_db] schema when install QC software
6. get QC repository backup files from D:\Backups\QCRepositoryBak after install QC software
7. Unzip and replace all of the files in new QC Repository path with these backup files. In fact the new path is same as the original one
8. startup QC service and login to QC Site Administration page, QC can list all of the available projects whose databases are correct to restored automatically
9. check if QC projects information is available in QC Site Administration page
10. check if QC projects data is correct after login the special project by project users
5.2 Informal QC Server Recovery
If the formal recovery process encounters errors or does not work, we have to conduct the informal recovery approach from QC setup.
1. restore all of the QC Project databases referred to 4.3.1 except database [qcsiteadmin_db]
2. restore database [qcsiteadmin_db] referred to 4.3.1, but need to change the restored database name to another one, for example [old_qcsiteadmin_db]
3. uninstall QC software and the entire QC files in QC server
4. invoke QC setup program to install QC software into any path
5. set SQL Sever connection as [sa] login when setup QC
6. set the correct [td] password to [qcsiteadmin_db] schema when install QC, and QC will create a new [qcsiteadmin_db] database in this SQL Server
7. stop QC service after QC installation is done, and we need to export Table data from [old_qcsiteadmin_db] and override into [qcsiteadmin_db] by the same Table Name by SQL Server export/import tool:
n mandatory tables
[td.USERS]
[td.ROLES]
[td.USER_ROLES]
[td.PARAMS]
[td.SEQUENCES]
n optional tables
[td.SESSION_LICENSE]
[td.SESSION_LICENSE_HISTORY]
[td.SESSION _ HISTORY]
8. startup QC service and login QC Site Administration page by a QC administrator user, and check QC Site Administration is available
9. Create all of the necessary Domains and Projects in QC Site Administration, and the empty databases are also created in SQL Server automatically. Note that their database names should be different with the databases name restored in step 1
10. stop QC service again
11. get QC repository backup files from D:\Backups\QCRepositoryBak
12. Unzip the backup file, replace the files under [sa] folder in the current QC Repository path with the backup [sa] files
13. Unzip the backup file, replace the files except [dbid.xml] file in every project folder under [qc] folder of the current QC Repository path with the files under the same folder of the backup files, such as [qc\starcite_3\smallmeetingsolutions]
14. Open [dbid.xml] file in every project folder, and update the value of [DB_NAME] to the restored database name, for example [starcite_smallmeetingsolutions]. If other values need to update, just do it
15. Open [qcsiteadmin_db] in SQL Server, and open [td.PROJECTS] Table; update [DB_NAME] field value to the restored database name, for example [starcite_smallmeetingsolutions]. If other fields need to update, just do it
16. remember [PROJECT_ID] field values for every project name, close [td.PROJECTS] Table
17. update the values of [PROJECT_ID] field of [td.USER_PROJECTS] in [old_qcsiteadmin_db] database to the new values known from step 16
18. export [td.USER_PROJECTS] Table data from [old_qcsiteadmin_db] and override into [qcsiteadmin_db] by the same Table Name by SQL Server export/import tool
19. startup QC service again
20. login QC Site Administration page by a QC administrator user, QC users should be related to the relevant projects as normal
21. activate these restored QC Projects and check them to associate the correct database in SQL Server
22. login QC project by the project user as normal
23. delete database [old_qcsiteadmin_db] and all of the created empty project databases in SQL Server
24. deploy SQL Server database backup strategy by Section 3
Section III Quality Center Upgrade Strategy
6. Quality Center Upgrade
If Quality Center would upgrade from version 9.2 to 10.0, we can follow Section 5 as well. Of course to keep all of the databases in SQL Server available is principal.
6.1 Formal QC Server Upgrade
1. uninstall QC 9.2 in QC server
2. invoke QC 10 setup program and install it into the same folder with the original QC path
3. if [qcsiteadmin_db] is restored or existing in QC Database server, choose [upgrade existing qcsiteadmin_db schema] when install QC software
4. use [sa] login to connect SQL Server when setup QC software
5. set the correct [td] password to [qcsiteadmin_db] schema when install QC software
6. get QC repository backup files from D:\Backups\QCRepositoryBak after install QC software
7. Unzip and replace the necessary files including [sa] and [qc] folder in new QC Repository path with these backup files. Note that [dbid.xml] need not replace
8. startup QC service and login to QC Site Administration page, QC can list all of the available projects whose databases are correct to restored automatically
9. check if QC projects information is available in QC Site Administration page
10. upgrade every project by QC [Upgrade Project] tool in Site Administration page
11. check if QC projects data is correct after login the special project by project users
5.2 Informal QC Server Upgrade
If the formal recovery process encounters errors or does not work, we have to conduct the informal recovery approach from QC setup.
1. restore all of the QC Project databases referred to 4.3.1 except database [qcsiteadmin_db]
2. restore database [qcsiteadmin_db] referred to 4.3.1, but need to change the restored database name to another one, for example [old_qcsiteadmin_db]
3. uninstall QC 9.2 software and the entire QC files in QC server
4. invoke QC 10.0 setup program to install QC software into any path
5. set SQL Sever connection as [sa] login when setup QC
6. set the correct [td] password to [qcsiteadmin_db] schema when install QC, and QC will create a new [qcsiteadmin_db] database in this SQL Server
7. stop QC service after QC 10.0 installation is done, and we need to export Table data from [old_qcsiteadmin_db] and override into [qcsiteadmin_db] by the same Table Name by SQL Server export/import tool; before that we should confirm these Tables of the 2 databases have the common structure:
n mandatory tables
[td.USERS]
[td.ROLES]
[td.USER_ROLES]
[td.PARAMS]
[td.SEQUENCES]
n optional tables
[td.SESSION_LICENSE]
[td.SESSION_LICENSE_HISTORY]
[td.SESSION _ HISTORY]
8. startup QC service and login QC Site Administration page by a QC administrator user, and check QC Site Administration is available
9. Create all of the necessary Domains and Projects in QC Site Administration, and the empty databases are also created in SQL Server automatically. Note that their database names should be different with the databases name restored in step 1
10. stop QC service again
11. get QC repository backup files from D:\Backups\QCRepositoryBak
12. Unzip the backup file, compare the backup files about [sa] folder with the current files, select the necessary files to replace the files under [sa] folder in the current QC Repository path
13. Unzip the backup file, compare the backup files about [qc] folder with the current files, select the necessary files to replace them except [dbid.xml] file in every project folder under [qc] folder of the current QC Repository path with the files under the same folder of the backup files, such as [qc\starcite_3\smallmeetingsolutions]
14. Open [dbid.xml] file in every project folder, and update the value of [DB_NAME] to the restored database name, for example [starcite_smallmeetingsolutions]. If other values need to update, just do it
15. Open [qcsiteadmin_db] in SQL Server, and open [td.PROJECTS] Table; update [DB_NAME] field value to the restored database name, for example [starcite_smallmeetingsolutions]. If other fields need to update, just do it
16. remember [PROJECT_ID] field values for every project name, close [td.PROJECTS] Table
17. update the values of [PROJECT_ID] field of [td.USER_PROJECTS] in [old_qcsiteadmin_db] database to the new values known from step 16
18. export [td.USER_PROJECTS] Table data from [old_qcsiteadmin_db] and override into [qcsiteadmin_db] by the same Table Name by SQL Server export/import tool
19. startup QC service again
20. login QC Site Administration page by a QC administrator user, QC users should be related to the relevant projects as normal
21. upgrade every project by QC [Upgrade Project] tool in Site Administration
22. activate these restored QC Projects and check them to associate the correct database in SQL Server
23. login QC project by the project user as normal
24. delete database [old_qcsiteadmin_db] and all of the created empty project databases in SQL Server
25. deploy SQL Server database backup strategy by Section 3