60

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


30

SQL Server keeps logs for each deleted record. You can query these logs via the fn_dblog SQL Server function. SELECT [RowLog Contents 0] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = 'dbo.TableName' AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' ) AND Operation in ( 'LOP_DELETE_ROWS' ) ; But this log is in Hex ...


23

Crash recovery is running on 'MyDb'. Possible causes: The SQL Server service was restarted manually, due to a crash or a server stop/start. A severe error in the database caused the database to be shut down and recovered. Recovery was initiated deliberately by someone executing a RESTORE WITH RECOVERY. The Auto-Close option is set, causing the database to ...


18

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


16

I am not sure if this would help solve the issue, but you can give it a shot. Run: RESTORE DATABASE YourDatabase WITH RECOVERY See if the above brings the database out of recovering mode. If it doesn't then the problem may be something else. Can you try the below command and see if the database is corrupt? DBCC CHECKDB ('YourDBname') WITH NO_INFOMSGS, ...


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

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


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


14

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


11

The backup contains two pieces of information: the database files layout and metadata, ie. RESTORE FILELISTONLY ... the actual data The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the ...


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

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


10

You will need a previous Full Backup and any log backups taken since the last Full backup 1) Take a tail of the log backup BACKUP LOG OldDB TO DISK='C:\OldDB_Tail.trn' WITH NO_TRUNCATE 2) Find Transaction for Deleted Records (Operation will be LOP_DELETE_ROWS for DELETEs and LOP_SET_BITS & LOP_MODIFY_ROW for a TRUNCATE TABLE) SELECT * FROM fn_dblog(...


10

Remember: you do not have a recovery plan until you tested your recovery plan... The theory is simple: on the disaster recovery machine you have prepared for such cases you restore the most recent full backup, then you apply the most recent diff backup (if any) and then all the hourly log backups taken after the most recent diff (or after the most recent ...


10

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

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


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


8

No. There is no way to recover the data. The LDF is most likely useless even with a 3rd party log reader tool.


8

Wait patiently for it to recover. This is what is supposed to happen. While waiting, try to track down your latest backup, just in case. I suggest a small investment in a UPS will avoid this problem in the future.


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 would not trust rsync to copy the ldf and mdf files for user or system databases, nor would I trust anything I hacked together (VSS or otherwise) in a production environment. SQL Server is very fussy about when (and if) things get written to the ldf and the mdf files. Software (rsync) that isn't designed with that in mind might not get it right, especially ...


7

Your only real chance is using a 3rd-party log analyzer tool, and even that may not work. SIMPLE recovery truncates the log when a CHECKPOINT process occurs, which is highly likely to have happened at this point. Also, if losing the entire set of changes between backups is unacceptable, either back up more frequently in SIMPLE (if your database is small), ...


7

I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then ...


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


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