When should I use the full recovery model and when should I use the simple recovery model for databases?
You should use the full recovery model when you require point-in-time recovery of your database. You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is ...
I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data.
Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities.
Go into your command/terminal to open the MySQL utility, ...
Since the server had been offline for a while we thought it may have gone outside the recovery window of the primary. We decided to try applying the latest transaction logs on the database to see if that would kick-start the recovery process:
-- Remove database from Availability Group:
Alter Database [StackExchange.Bicycles.Meta] SET HADR OFF;
-- Apply ...
For a MyISAM table mydb.mytable, you should have three files
They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are ...
The standard procedure would be to:
Obtain the page IDs that have to be restored.
Start a page restore with a full database.
Apply the most recent differential backup.
Apply subsequent log backups.
Create new log backup.
Restore the new lob backup.
After the new log backup has been applied, the page restore is completed and the pages are then usable.
No, you need to have taken transaction logs that cover the time frame you want to use with STOPAT. You can't do this from a full database backup only - that is just a one-time copy, and that is why we have different types of backups (full, log, diff).
If you have taken transaction log backups in between your full backups, please update the question with ...
The answer of eoinbrazil is partly incorrect. A new Node can be in STARTUP2 for a long time. The link the posted says:
Each member of a replica set enters the STARTUP2 state as soon as mongod finishes loading that member’s configuration, at which time it becomes an active member of the replica set. The member then decides whether or not to undertake an ...
If a disaster occurred and I needed to recover the database from backup, would I be missing data?
As long as all of the backups are in tact, no.
The transaction log chain is not broken, and point-in-time recovery is possible.
It's just that the backups that constitute a complete transaction log chain are not all in the same location.
Having said that, I ...
After googling for hours, I stumbled across a thread that wasn't really related to my issue, best I could tell, but it seemed harmless enough to try, and voilà!
I looked at /var/lib/postgresql/9.1/main/pg_clog, and saw:
drwx------ 2 postgres postgres 4096 Mar 15 15:20 .
drwx------ 13 postgres postgres 4096 Mar 25 12:15 ..
-rw------- 1 postgres ...
Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?
No, restoring a transaction log is sequential. Transaction log relies on LSN (Log Sequence Number)
Also, you cannot restore your database with just transaction log. It ...
Could anyone confirm or denies that restoring the header only wouldn't
affect the sys.fn_dblog or anything else?
RESTORE HEADERONLY doesn't target any database in its syntax:
RESTORE HEADERONLY FROM <backup_device>
It reads the backup device and returns data to the client about what's in the backup. It won't affect your databases in any way.
I'm switching a lot of databases to SIMPLE recovery mode from FULL recovery mode (T-Logs and point-in-time recovery is not necessary). Will the existing transaction logs be truncated (when checkpoints are created)?
In simple recovery model, the database engine will issue automatic checkpoints and its frequency is determined by the recovery interval (...
If your database is in full recovery mode you can also try third party tools such as ApexSQL Log or SQL Log Rescue.
These tools will attempt to read your transaction log and reconstruct statements.
You can also try reading transaction log manually using fn_dblog function but it’s going to be complex since this is not a well-documented function.
In order to understanding why error 3456 would be thrown, we need to take a little step back and understand how SQL Server handles this corner of recovery.
When SQL Server is redoing an operation, and that redo is a page modification, it makes a quick check. In the page header there is ultimately going to be a PageLSN, which is an indication of the last ...
Yes, you would have to restore the full backup, at maximum one diff backup, and all necessary log backups to perform a point-in-time recovery in SQL Server. In SQL Server, you cannot roll back already committed transactions to go back in time. You would likely want to use the STOPAT syntax to restore your database to the exact desired point in time.
Turns out it can be done if you use CONTINUE_AFTER_ERROR
RESTORE DATABASE foo WITH RECOVERY, CONTINUE_AFTER_ERROR
I did still get a warning when I tried it but then did a CHECKDB and received no errors.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE ...
Following is a short graphic which I will be using to explain when orphans are created in the incarnations of a database. It is a variation of the graphic I used to explain incarnations in my answer to the question Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?
I hope you enjoy the journey.
I'll explain how commercial tools work, on the ApexSQL Log example
And on related note I have heard that there are commercial tools to “rollback/undo” standard queries using full recovery LDF file. How do they do it? Do they analyze the LDF contents and try to come-up with inverse/undo operations?
Yes, they read the LDF file (online or detached) and trn ...
I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to perform recovery.
This is not totally correct when a transaction starts in SQL Server it reserves space in transaction log in case the transaction has to rollback. From Transaction Log architecture BOL ...
ORPHAN if this is a noncurrent incarnation that is not a direct
ancestor of the current incarnation.
Steps to reproduce:
SQL> select incarnation#, status from v$database_incarnation;
SQL> select current_scn from v$database;
The Comparison of RECOVERY and NORECOVERY section of the RESTORE Statements doc should help you with that question:
Rollback is controlled by the RESTORE statement through the [ RECOVERY
| NORECOVERY ] options:
NORECOVERY specifies that rollback doesn't occur. This allows rollforward to continue with the next statement in the sequence.
In this case, the ...
I've never had any issues from switching from simple to full. You will have to consider your backups tho.
Microsoft has some information about making the switch in the link below,
What was suggested to you thus far is what can be done to bring the database to a consistent state.
Here is what you need to know about InnoDB.
First of all here is the InnoDB Architecture in Pictorial Form
Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?
The Double Write Buffer has the ...
Check the MySQL error log. It looks like trying to read table 44 is actually crashing the server.
"Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA because it often means that whatever your query just did has actually crashed the server.
The subsequent messages seem to bear this out:
mysqldump: Got ...
Restart the db mirroring end point:
ALTER ENDPOINT<Endpoint Name> STATE=STOPPED
ALTER ENDPOINT<Endpoint Name> STATE=STARTED
TECH note: if there are multiple databases, not just the one in error, then stopping and starting the enpoint affects all the databases on that endpoint. This can cause problems on a production ...
There are multiple ways to get to the intended target recovery point with your setup.
A few things:
You cannot eliminate log backups for the most important reason of transaction log re-use.
It's not possible to know when an issue will occur and thus having multiple ways to get to the RPO is useful and sometimes necessary.
Differentials aren't a substitute ...
Is there any way at all to restore these files?
The long answer is like this:
you first will have to fix the MDFs. Attempt to attach it with ATTACH_REBUILD_LOG
as you encounter errors (like incorrect pageid (expected 1:134; actual 0:0). It occurred during a read of page (1:134)) you have to edit the file and fix it. This, of course, requires you to ...
Ensure that the MYSQL_HOME path is being exported in the .profile. If the MySQL install is in a different location, then make that change to the MYSQL_HOME.(Example: MYSQL_HOME=/path/to/mysql)
Crash Recovery Steps:
Find a valid seqno. Look at the grastate.dat file on each server to see which machine has the most current data. The ...
You could look into using the SQL Server snapshot feature depending on your use case. I use the feature prior to running production deployments and in non-Prod environments when I want to reset a DB to match specific test starting conditions. Since it utilizes sparse files, only copies modified pages to maintain the snapshot and allows you to maintain 1+ ...
I have answered questions like this before
Dec 21, 2011 : Table compression in InnoDB?
Feb 03, 2012 : How to recover MySQL table structure from FRM files
Apr 23, 2012 : restore table from .frm and .ibd file?
I have referred to a blog from Chris Calendar on connecting .ibd files back to the data dictionary.
If you do not have the .ibd along with .frm, ...