23 votes
Accepted

Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

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 ...
user avatar
  • 441
20 votes
Accepted

Availability Group database stuck in Not Synchronizing / Recovery Pending mode

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 ...
user avatar
  • 1,003
16 votes

Online page restore hitting 1000 limit

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. ...
user avatar
  • 15.2k
14 votes

Why is Mongo stuck in STARTUP2?

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 ...
user avatar
14 votes

Restore database to point in time from full backup only

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 ...
user avatar
11 votes

SQL database stuck in recovery

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 ...
user avatar
  • 14.6k
10 votes
Accepted

Is it possible to restore SQL Server DB from transaction log (no MDF)?

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, ...
user avatar
  • 61.3k
10 votes
Accepted

Decrypt T-SQL log backup header and read LSN

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 ...
user avatar
9 votes
Accepted

Unable to restore (error 3456)

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, ...
user avatar
9 votes
Accepted

Can I recover a SQL database from Standby/Read-Only without a standby file?

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 ...
user avatar
9 votes
Accepted

Is there a way to reduce the amount of time spent on a point in time recovery?

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 ...
user avatar
  • 14.6k
8 votes

Contents of transaction log file in more detail

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. ...
user avatar
8 votes
Accepted

What is an orphan incarnation?

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 ...
user avatar
  • 15.2k
7 votes
Accepted

Why does database recovery take so long?

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 ...
user avatar
  • 18.4k
7 votes

What is an orphan incarnation?

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$...
user avatar
  • 38.4k
7 votes
Accepted

How crash recovery process works in SQL Server?

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 ...
user avatar
  • 4,589
6 votes

SQL Server mirrored database is stuck in recovery mode

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 ...
user avatar
  • 61
6 votes
Accepted

FULL recovery and differential backups

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 ...
user avatar
6 votes

How to recover deleted records by mistake in Mysql

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 ...
user avatar
  • 4,837
6 votes
Accepted

Reattaching MDF files recovered from RAID 5

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 ...
user avatar
6 votes

Automated regular tests of your backups

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 ...
user avatar
6 votes
Accepted

How to recover MariaDB Galera Cluster after full crash?

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=...
user avatar
6 votes

Is there a way to reduce the amount of time spent on a point in time recovery?

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 ...
user avatar
  • 1,913
5 votes

Recover database from uninstalled SQL Server

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 ...
user avatar
  • 2,201
5 votes

Using Ola Hallengren's full backup but database option is for simple?

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 ...
user avatar
5 votes
Accepted

Is there a problem creating only differential backups every hour?

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 ...
user avatar
  • 67.5k
5 votes
Accepted

Strategy on Standby restore option for hourly log shipping file

Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode? Either will work, but it is faster to apply all the logs except the last ...
user avatar
5 votes

Starting SQL server Database in forced recovery mode

Not possible. Recovery mode means that redo was performed but not undo i.e., you need a prior restore operation. A snapshot backup can be used as a base for restoring log backups (Veeam does it) but I ...
user avatar
5 votes

Should databases be dumped to SQL format or custom archive?

Ask yourself the same question that you ask whenever you need to store any Data: How are you going to use this Data Backup? If you need to get "into" the file and manipulate it (as a ...
user avatar
  • 6,803
4 votes
Accepted

Differential/T-Log LSN question

I will answer you question point wise like you asked No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed ...
user avatar
  • 18.4k

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