62

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


21

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


20

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


18

MyISAM For a MyISAM table mydb.mytable, you should have three files \bin\mysql\mysql5.6.12\data\mydb\mytable.frm \bin\mysql\mysql5.6.12\data\mydb\mytable.MYD \bin\mysql\mysql5.6.12\data\mydb\mytable.MYI 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 ...


16

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


14

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


13

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


11

The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction. ...


11

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


10

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


10

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


10

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.


9

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.


9

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


9

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


9

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


8

At TwinDB we built a web interface to mysqlfrm. It's free and easy to use. To recover table structure from you just need to upload the .frm file. Here are steps. 1. Open https://recovery.twindb.com/ . Click on "Recover Structure" In next submenu click on "from .frm file" On the next view click on "Browse" and select an .frm file on a local disk. Click on ...


8

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


7

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


7

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


7

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


7

RC_DATABASE_INCARNATION 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; INCARNATION# STATUS ------------ ------- 1 PARENT 2 CURRENT SQL> select current_scn from v$database; CURRENT_SCN ---...


7

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


6

Seeing as you have stated that the original data files look like they're untouched, all you need to do is recreate the service with oradim, making sure you're using the 9i ORACLE_HOME service and tools.


6

Sorry to be brief, but once you get this database restored (whether you can fix the suspect or have to revert to an older backup): Move to a host that doesn't shut down for the day, or that at least gives you a way to access backups without having to talk to them. You need to implement a better recovery plan for your "most important databases"... your ...


6

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


6

Restart the db mirroring end point: --To stop ALTER ENDPOINT<Endpoint Name> STATE=STOPPED --To start 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 ...


6

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


6

Is there any way at all to restore these files? No. 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 ...


6

Pre-Recovery Settings: 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 ...


Only top voted, non community-wiki answers of a minimum length are eligible