Backing up HP ALM Quality Center version 11

Hi,
  Does anyone have experience in HP ALM Quality Center version 11?  The previous tech left so there is little info. about this setup with no support contract.
 Currently everything for Quality Center (JBOSS, Microsoft SQL Server 2008 R2, Win 2008 R2 Ent. ) is installed in one VM and it's one serves this function.

Any recommendations on a backup and restore plan should anything goes wrong with this server or on the HP ALM Quality Center version 11 application?
qc.jpg
PJ ChapmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
I haven't used  HP ALM Quality Center version 11 before, but I should be able to advise on a backup strategy and recovery practices -- if that is what you're looking for.  Is it just everything sitting on one VM -- application and SQL Server?  What are your business recovery requirements?  If you could provide a few more details in this regard, I'm sure I can put some suggestsions together for you.
PJ ChapmanAuthor Commented:
Hi dbaSQL,
   Yes, everything is installed and sitting under one VM, for this particular server there really isn't any recovery standard, as it is only used for by a small group.
The backup and recovery solution would be for me to cover myself should it falls apart.

I've asked the IT for a snapshot of the VM, however the IT's policy is to only keep the snapshots for up to 2 weeks time.
dbaSQLCommented:
>>The backup and recovery solution would be for me to cover myself should it falls apart.
It sounds like you don't need anything very advanced, like clustering, replication or log shipping.  The most simple approach is a backup/restore solution, which can be done very easily.   In short, you've got two things to worry about:  Backing up the database, and restoring it.

Before worrying about the backup, you have to determine where you will be restoring the database(s).  Here you will use a 'warm' backup server, or a 'cold' one.  The warm instance is preinstalled and preconfigured, waiting for your use.  The cold one is a spare server (or VM) just sitting and waiting for use.  The cold server is more timely because you have to install and configure SQL Server before you can restore your databases.  Your business will determine which one of these you get, but the warm one provides a faster recovery.

Now you setup your backup strategy, which is based on your recovery requirements.  Will last night's backup be sufficient, or do you need more point-in-time recovery to allow you to recover data much closer to the time of failure.  I will give  you an example for both, to a warm server.

#1  FULL backups - runs once daily @9PM
Your VM goes down irrecoverably.  In this case, all you will need to do is restore last night's FULL bak file(s) w/these statements:
-- 1.  Use FILELISTONLY to return details from your bak files that are necessary in your RESTORE statements.
 RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\Backup\YourDB_FULL.bak'

--  2. Restore bak file(s)
 RESTORE DATABASE YourDB
 FROM DISK = 'C:\MSSQL\Backup\YourDB_FULL.bak'
 WITH 
   MOVE 'YourDB_Data' TO 'D:\MSSQL\Data\YourDB_Data.mdf', ---< new drive location, change accordingly
   MOVE 'YourDB_Log' TO 'D:\MSSQL\Log\YourDB_Log.ldf',    ---< new drive location, change accordingly
   RECOVERY; 

Open in new window

#2 -  FULL backup created each Sunday
      - Differential backup each day at 1AM (much smaller/quicker than Sunday full)
      - Transaction log backups every hour
Your VM goes down irrecoverably on Friday at 7AM.  Here you will need to restore the FULL, the DIFF and however many transaction log dumps have occurred since your last DIFF.
-- FULL BACKUP W/DIFFERENTIAL & TRANSACTION LOG
-- 1.  Use FILELISTONLY to return details from your bak files that are necessary in your RESTORE statements.
 RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\Backup\YourDB_FULL.bak'

-- 2. Restore bak file(s)
 RESTORE DATABASE YourDB
 FROM DISK = 'C:\MSSQL\Backup\YourDB_FULL.bak'
 WITH 
   MOVE 'YourDB_Data' TO 'D:\MSSQL\Data\YourDB_Data.mdf', ---< new drive location, change accordingly
   MOVE 'YourDB_Log' TO 'D:\MSSQL\Log\YourDB_Log.ldf',    ---< new drive location, change accordingly
   NORECOVERY; 

-- 3. Use FILELISTONLY to identify the DIFF backup filename: 
 RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\Backup\YourDB_DIFF.bak'   

-- 4. Perform the DIFFERENTIAL restore
   RESTORE DATABASE YourDB
   FROM DISK = 'C:\MSSQL\Backup\YourDB_DIFF.bak'
   WITH
      MOVE 'YourDB_Primary' TO 'D:\MSSQL\Data\YourDB_Primary.mdf',---< new drive location, change accordingly      
      MOVE 'YourDB_Log' TO 'D:\MSSQL\Log\YourDB_Log.ldf',		  ---< new drive location, change accordingly
      NORECOVERY; 

-- 5. Restore transaction log  --     NOTE:  MUST be run 7 times -- once for each log backup from 1AM to 7AM. 
    RESTORE LOG YourDB
    FROM DISK = 'C:\MSSQL\Backup\YourDB_LOG.bak'
    WITH NORECOVERY;

-- 6. Restore WITH RECOVERY
   RESTORE DATABASE YourDB WITH RECOVERY

Open in new window


NOTES:  

The bak files should be written remote from the targeted SQL instance, or copied off of the server very quickly after the backup is performed.  VERY IMPORTANT;  If your bak files are local, you have a single point of failure.  Your server goes down for good and you cannot recover, because you cannot access your backup files.  No good.

ALL of the above can be scripted;  Your backups should running automatically via SQL Server Agent, and are accessible for recovery when a failure happens.  You can proceduralize the RESTORE statements as well, but you can also accomplish the same just by editing the code with the correct filenames and directory locations, as needed.

Remember, the above is for a warm backup server.  SQL Server is already installed and configured, and you've got all of your logins in place as well.  This is one of the best references for copying logins from one server to another, and it should be used ahead of the time when you are bulding your warm server.  You can also run it and save the output to be used against the cold server, if needed.  https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

This has nothing to do w/your HP ALM.  It is universal, you could say, to any SQL Server, but your configuration and scheduling will be dictated by your application and your business recovery requirements.   How much does your data change, and how frequently?  You need to become very familiar with your data to determine the best backup strategy AND schedule for your organization.


These are just some very general guidelines.  Hopefully this has helped.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

PJ ChapmanAuthor Commented:
Wow! dbaSQL, thank you so much for the detailed backup and restore plan.  I really appreciate your help on this.
PJ ChapmanAuthor Commented:
Thank you very much.
dbaSQLCommented:
No problem at all, PJ.  Glad to have helped.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.