Solved

test database backups - to where?

Posted on 2013-11-25
7
387 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 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