Solved

test database backups - to where?

Posted on 2013-11-25
7
370 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 76

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 76

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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