Solved

test database backups - to where?

Posted on 2013-11-25
7
361 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 36

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:ScottPletcher
ScottPletcher 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now