Solved

test database backups - to where?

Posted on 2013-11-25
7
376 Views
Last Modified: 2013-12-02
can anyone give a beginners guide on how you actually test restores of database backups (be that sql or oracle), on the actual production database server. for example you may wish to test the backups work - fine, but if you do that on the production db server itself, wouldnt it just overwrite the live database - i.e. revert it back to a backed up state from say a week ago.

So do you do test restores onto test servers (and not production)- and is classed as sufficient as you still havent tested it on the production database server which is where youd have to restore it to in case of emergency, as you still havent proved the backup can be restored onto the production db server in case of emergency, but you have proved it can be restored to a test server (or is that sufficient ie if it restores fine on a test db server it will restore fine on a production db server).
0
Comment
Question by:pma111
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 100 total points
ID: 39674828
In Oracle it's easy enough to test recovery on a test server.

Given the size of the production database this may not be feasible.  Then you test the backup and recovery 'process' on a test server and then from time to time pull a file from the production tapes and make sure it appears to be OK.  Maybe with a chksum or something similar.

The thought here is:
If you know you can recover form the processes you have in place in test and you can pull valid files back off of tape, then you should be able to recover in production.
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 100 total points
ID: 39674829
Restoring it onto a test server is sufficient for me.  As long as you are taking care to keep the OS and DB versions the same as your production environment, in my opinion, you have proven your backup and restore process.  Any other action (restoring under a different name or restoring over your production DB) would impact production performance or availability negatively and would defeat the purpose.
0
 
LVL 3

Author Comment

by:pma111
ID: 39674845
Have you ever had any database backups that you couldnt restore onto either test or production - and did you ever identify why they couldnt be restored/common issues that mean backups cant be restored (hence the need for testing/resolution of any faults before it comes to crunch time when a production restore is required).
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39674858
>>Have you ever had ...

Yes.  It was the fault of a DBA that got tapes out of rotation and overwrote a tape containing a required archived redo log.  In Oracle, you miss one, you can only restore to the point of the missing one.

Luckily we could force the database open and do a full export, rebuild the database and import all the data back in.  That was a fun 72 hour marathon with several layers of management constantly standing behind me asking if it was done yet.

I have also had tapes snap.  Anyone that has been around for a while and dealt with tape has...

When testing recovery, as the admins are about ready to put a tape in, tell them that you have tagged is as 'broken' and they cannot use it.

Want some real fun:  Do it to two tapes in a row.

This is why my 'important' files like archived redo logs are ALWAYS on a few tapes.
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
ID: 39674977
>> with several layers of management constantly standing behind me asking if it was done yet
yeah, that's always the best part.

btw,
you can get management to do anything for you at that point in time
> telling them to go get a pizza always helps to make themselves feel useful and off your back ...


if you can do a restore on a test server, you have a big chance of being capable of doing a restore on a production server.
or at least providing a restored database which can be accessed on another server
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39675213
In SQL, you can easily restore to a different db name.  For example, a backup of database "abc" could be restored to database name "abc_test_restore" to test the restore process.

[I'm not sure if/how in Oracle you restore a schema to a different schema name.]
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 100 total points
ID: 39675702
[I'm not sure if/how in Oracle you restore a schema to a different schema name.]
Easy from an export or datapump dump backup.

From rman backup you would need to:

1) Restore the database in tablespace mode, the: SYSTEM, SYSAUX and UNDO tablespaces as well as the other tablespaces containing the data and indexes owned by the schema.
2) export the schema (exp or datapump).
3) import into the other database schema.
:p
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question