Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

test database backups - to where?

Posted on 2013-11-25
7
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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