Tag Info

Hot answers tagged

61

Is your database in full recovery mode? If yes, are you doing transaction log backups? If yes, do you have a backup utility like Quest LiteSpeed, Red Gate SQL Backup, or Idera SQLSafe? If yes, those utilities can undelete objects from backup files (including the fulls and transaction logs) - but explaining how to use those is beyond the scope of what ...


13

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


13

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


12

Unfortunately, we are not going to be able to help you without a lot more information. But from your question, I see that you have deleted what appears to be 2 million records from your database. Most likely you can't recover this information, unless you have full logging on your database and you purchase some very specific tools. If you can describe in ...


11

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


10

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


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


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


8

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


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

Expanding on my answer above, and making it a real answer... 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 ...


6

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.


6

You may create new controlfiles with a CREATE CONTROLFILE statement including a list of data files and redo logs. startup nomount CREATE CONTROLFILE REUSE DATABASE TEST RESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 1 MAXLOGHISTORY 449 LOGFILE GROUP 1 '/u01/oradata/test/redo01a.rdo', ...


6

First, to answer the questions directly: In my personal experience, if I were keeping score, causes for database restores have been: accidental deletion of data = many, restore to dev for testing = many, IO system failure / corruption = none. (Knocks on wood) You indicate you have "no need to recover to any point in time previous to our last backup". If ...


5

Instance Recovery is necessary to get the database back to a consistent state after shutdown abort or some other abnormal shutdown event. Oracle is never going to let you open the database in an inconsistent state, so no, there is no possibility that you can disable it. If Oracle is crashing during Instance Recovery you need to get in touch with Oracle ...


5

It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default ...


5

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


5

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


5

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


4

First of all export is not backup. You will not recover a physical copy of a database from exp-dump, only an unconsistent copy of its data (you can make them consistent but the option is rarely used). I'm really not sure you can apply the contents of logMiner on such a copy. Your best bet would be to apply undo statements to your current copy of the DB to ...


4

Sorry, that you had such an accident. If you have binary logs on, you could try the recovery options described here: Point in time recovery Another thing is to restore a backup, hopefully, you have one. Changes made after this, could be restored as mentioned above. Good luck! vorax


4

The minimum deltas you have to ship over is ... the log shipping delta! RSync will not reduce the delta, it will increase it. Log shipping does not need to ship over any MDF delta, because the the standby knows how to interpret the LDFs delta into actions to be applied to the local MDF (ie. by running recovery with standby on the log it received). 101% of ...


4

I would do a point in time recovery to a different location, restoring to the desired time, and pg_dump the problem database. I would drop the one database on the normal location, create it again, and load the pg_dump output. Make sure you make and save a file-system level copy of the cluster's data directory tree before you start anything like this.


4

From a chat discussion, the first error is because the file ./reportingdb/bigdata_banner_scheduler.ibd is missing. Just copying this file from the master won't work, though. You'll need to drop the table on the slave and then dump the table from the master. But that assert error is a different matter. You can start in force_recovery mode 1, but something is ...


4

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


4

Yes, it's fairly simple. You need to copy the database from the old disk image to a directory in the new host then start it using the same major version of PostgreSQL compiled with the same options (integer datetimes on/off, etc). You can start the DB with a command like PGPORT=5433 pg_ctl -D /path/to/copy/of/old/database start. You must use the same ...


3

Your gut instinct is correct. Windows and SQL Server are designed around objects and APIs, whereas Unix is designed around text and files. At a philosophical level, using rsync – a Unix tool built around files – to patch up SQL Server databases – well-defined objects with set APIs – is wrong. At a technical level, using rsync to apply MDF/NDF/LDF ...


3

It is considered good practice where I work to run DBCC CHECKDB after a restore, especially if the backup is of an unknown quality. CHECKDB will at least tell you if your DB has any consistency errors, and checks the logical and physical integrity of all the objects in the specified DB. A similar thread is on ServerFault: ...



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