22

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


19

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

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


11

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

The error you're seeing in the SQL Server Error Log is this one: Recovery of database 'CrashTestDummy' (9) is 0% complete (approximately 42 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required More generically, it will say: Recovery of database '{Database Name}' ({Database ID}) is {N}% complete (...


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

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

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


8

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


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

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, http://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx


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


6

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


5

If it's MyISAM - no chance to recover w/o a backup. If InnoDB - it depends. InnoDB flags a record as deleted and keeps it in a page for a while. When a tree is rebalanced the deleted records are purged. So whether you can undelete records depends on how much writes were done to the table after the delete. Open InnoDB tablespace in a hexeditor and try to ...


5

Provided that you have alerts set up on the jobs, Log Shipping is a feature that is available on all editions, doesn't require a whole lot of effort to set up, and I believe fulfills all of your requirements. I would avoid any wizards that you might stumble upon in Management Studio. Essentially you need three jobs for the following tasks: Initialize the ...


5

The database files are probably still there - in the directory for data and log files. Th default one is c:\ProgramFiles\MSSQL\MSSQL\MSSQL10_50\MSSQL\Data. If not default then you need to locate your files and attach them to SQL Server instance of the same or higher edition.


5

A full backup can be taken from a database in any recovery model (full, simple, or bulk-logged). It is an unfortunate conflict in terms that is somewhat overloaded. You could consider this type of backup to be a single, point-in-time, snapshot of a database that can be restored on its own in entirety. The recovery model just dictates how logging works and ...


5

Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups. If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours ...


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