How to Recover SQL Database Without Log File: An Ultimate Guide
“Recently I was trying to recover a database that contains MDF and LDF data files. All of a sudden, I got .mdf file from a standard backup tape. However, sp_detach_db was not run on database before the backup of MDF file, so I do not have an LDF file. I know the stored procedure sp_attach_single_file_db can recreate a log file in most cases, and I have tried it to reattach the database, but I receive the following error. If anyone know the solution on how to recover SQL database without log file, please help me out.”
Are you also getting the same error message? Searching for an instant or reliable method to fix it? Do not worry, you are landed on the perfect page. There are multiple users who are facing this problem. Therefore, after understanding the above scenario we have come up with a manual solution. Before that, let us discuss all possible reasons for repairing SQL database without log file.
Reasons Behind Recovery of SQL Database Without LDF file
Go through the following reasons due to which users need to recover database .mdf file without .ldf. Some of them are listed below:
- Most of the people do not know that log (.ldf) file contains precious transaction data and it is required.
- There are cases where log file too bit and users have never cared for its size and content. When they want to transfer database to another server they only want the .mdf file to be moved to a new instance.
- The log file gets corrupted due to hardware failure.
Ways to Recover SQL Database Without Log File
There are different methods through which uses can recover SQL database without using transaction log file. Follow the set of instructions that are listed below:
Way #1: Using T-SQL Method
You can run sp_detach_db on the database to reattach a database with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures the transactional consistency within a database and retains the data integrity. However, if the data integrity is not required or no data has been changed, you will be able to use undocumented Database Consistency Checker (DBCC) REBUILD_LOG cmdlt that Listing 1 shows to attach a database. REBUILD_LOG will re-create the another log file and reattach a particular database even if a valid log file does not exist. But, the data might not eventually consistent because you could have thrown away active and uncommitted transactions. Use this script only for emergency recovery when you need to move data to another database.
Way #2: Using SQL Server Management Studio (SSMS)
Be sure that the following steps will work only if the database has been clean shutdown and primary file (.mdf) is available. Please have a look:
- Under Object Explorer window, right click on Database and go to Attach option
- On the Attach Databases, a dialog box appears and click on Add
- The dialog box Locate Database Files appears, browse the location where MDF file is located, hit on file to select and then press OK button to exit. A new log file is created by SQL Server while repairing the database. Now, the database will appear in Databases
- Now, back to the Attach Database box. In the database details, you will observe that SQL Server is unable to find log files (.ldf).
- To attach the MDF file without LDF, choose the transaction log file and after that, click on Remove
Expert Solution to Repair SQL Database File Without LDF File
As we look at the different methods to recover SQL database without log file. It is more clear that all these manual ways have their respective drawbacks. These methods do not provide the satisfactory results. Thus, we have come with a remarkable tool named as SQL Database Repair Tool. It is the best solution that helps to repair both primary (.mdf) and secondary (.ndf) SQL database files. In addition, it is capable enough to restore deleted SQL database table’s without any hassle. It has simple, easy-to-use and user-friendly interface which makes easy for users to accomplish the task effectively.
Final Thoughts
While working with Microsoft SQL Server, the situation arises in which database got corrupted. Now, it is necessary to recover all the corrupt database files in SQL Server. Thus, in this blog, we have discussed both manual and professional solutions to let users understand how to recover SQL database without log file. Also, we have covered the most important features of the tool in an absolute way.
Get Rid of SQL Server Error 8992 Instantly
Recently while resolving users queries , I got to know one error which users are facing and face problem in fixing it. See query:
“I ran DBCC CHECKDB over the database and got following error:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=xxxxxxx) of row (object_id=xxxxxx,column_id=11) in sys.columns does not have a matching row (object_id=xxxxxxx) in sys.objects.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. ”
Please help us out in resolving the issue without affecting the users.
Users facing SQL error 8992 need not to be worry. Here I am going to resolve SQL Server Error 8992. But first, Let us understand what issues you will be facing while you are getting SQL error msg 8992.
Why SQL Server error 8992 occur?
The reason for getting SQL error code 8992 could be any of the following:
- You might be facing inconsistency in your system metadata when you update your SQL Server database.
- Or maybe you might get this error when you update the system tables in SQL Server and run DBCC CHECKDB or DBCC CHECKCATALOG command.
Reason of Getting SQL error 8992
The error code 8992 occurs when SQL Server does not support the manual updates to system tables. Remember it must be updated only by the SQL database engine.
The error comes when DBCC CHECKDB can’t repair metadata corruptions.
Resolving SQL Server Error 8992
You have various options to repair SQL error 8992. You can choose it accordingly to your situation and get it resolved.
- So, if you have clean backup which is free from any inconsistencies, you can restore it from the backup. Here are the steps:
- Click on the name of the Database you want to restore.
- Right click on the Database, Click Restore Database.
- Check ‘From Device’ option. Browse the location of the .bak file.And select the Type of Backup Media as File.
- Select the database you want to restore and Check the Restore option.
- In the Options pane, Select Overwrite the existing database(WITH REPLACE) under Restore options.
- And click RESTORE WITH NORECOVERY under Recovery state section.
- Click Ok. You have successfully restore your data from the clean backup.
- But what if you dont have backup , for that case you can export the data to new database. After that migrate all the content of the updated database to new database.
Well you might be thinking, what about inconsistencies found in the system catalogues? The answer is you cannot repair the inconsistencies in DBCC CHECKDB by using REPAIR options. The repair command is the minimum level of corruption and it does not guarantee repairing your corruption.
Now let us imagine the worst case, you don’t have backup, and you have inconsistency in your database. So what will you do now??
Fix SQL Server error 8992 without any Data loss
So if you are in the worst case, the best way is to go for an professional approach ie SQL Repair Tool. It will remove and repair any type of inconsistencies found in the MDF / NDF File. You can try FREE demo version of this software. It supports MDF File version of 2017, 2016 & all its below version.
Conclusion
If you are looking to repair SQL Server error 8992, you are on a right place. The blog discusses the reason of getting this SQL code 8992 and every possible solution to fix SQL Server error 8992.
Truth Behind SQL Server Transaction Log Misconception
Logs plays an important role in SQL Server Database. Transaction log records all transaction activities done in SQL Server database. After every modification in transaction, a log record is written to the transaction log file.
Here we will be discussing on various transaction log myths which various DBAs and users think that they are true. Let us discuss misconception around transaction logs.
#Myth1- Transaction Log truncation make the log file smaller.
Reality – No, Truncation does not reduce the size of physical log file. At the time of truncation, only the active portion is scanned. Some parts are marked as inactive and they are used as free space to write down new transaction. There is no change done in the size of transaction log as the parts which are inactive remain intact and nothing is removed or deleted.
#Myth2- No need to take log backup for disaster recovery, if you are taking full backup daily.
Reality – This is wrong! Full backup does not mark log file as reusable. It depends on the amount of data you can afford to loose. If you don’t worry to loose your transaction log data, then you should use Simple recovery model. Regular taking transaction log backup does not allow you to afford lost transaction log data.
#Myth3- SQL Server is too busy. I think, I don’t need to take backup of SQL Server Transaction log
Reality – This is completely untrue! If your SQL Server is too busy, you should take more frequent backups. If you are not taking regular SQL transaction log backup, the transaction log will become full resulting in growth of transaction logs. Busier the SQL Server, more frequent you should take log backups. Taking regular log backup does not block transaction log, but result in an Auto growth event.
#Myth4 – If I perform full backup, I Don’t need to perform log backup for point-in-time restore.
Reality- This is one of the common misconception every user is believing. The reason for this myth is RESTORE command used with STOPAT clause. STOPAT clause specifies point in time for RESTORE LOG command. This command works well when it is used with log backups. Hence it can also be used with full database backup, it is clear that transaction log backup are not needed to recover at specific point in time.
#Myth5 – Shrinking frees space in SQL transaction log so taking transaction log backup is not necessary.
Reality – Shrinking operation is not a good practice. It does not resolve the log size issue. The transaction log will grow again, after performing initial shrinking operation.Auto-growth event must be avoided. You can maintain the size of transaction log by performing regular log backup. Or if you afford data loss, you can set recovery Model as Simple Recovery Model.
Want to read Transaction Logs Quickly?
You can read transaction log in SQL Server by using SQL Server Transaction log reader. It previews all the log activities like Transaction , Time, table name , query. The software fetch & preview records from Live database and it works both as online and offline SQL database environment.
Conclusion:
Transaction logs plays crucial role in SQL Server Database. The blog covers misconception around transaction logs in SQL Server. It also discusses a quick way to read transaction log activities in SQL Server.
Simple Way to Find Transaction Log Activities in SQL Server
“I am using SQL Server 2005. When I was doing modification to my database, I found that some of the data has already been modified. I want to find out the person who did changes to my database. Kindly help me in finding how to check transaction log in sql server 2005. I want to know the name of the person who did the changes along with the transaction date and time.”
There are many people who got stuck when comes to finding a transaction in a log for a particular user. In this case, SQL log file help user to examine all the transaction activity done in the SQL Server. In this blog we will discuss how to find transaction log in SQL Server.
Understanding Transaction Log in SQL Server
SQL Server database consists of transaction log that records all transaction activity like transaction time, transaction name, table name in LDF file. It also records each and every database modifications made by person. So, when a person did any modification in the database, then it becomes easy to identify the person who did changes in a log file. Reading logs in SQL Server is not an easy task, so here we will be discussing an easy & quick way to find out the transaction in SQL Server.
Quick way to Check Transaction log in SQL Server
Many business decision-makers face problem in connection with database due to many reasons such as sudden system shutdown, delay in troubleshooting, control audits or changes in employees. In such case, sometimes, they need to recover accidentally deleted data, track unwanted changes done in the database and find out the name of the user that has changed the data. To keep in mind all the things, one easy & quick solution to track down the transaction in SQL Server is by using SQL transaction log reader. It analyze all the transaction details like transaction name, transaction time, table name, query in SQL Server.The tool also offers many advanced features like fetch and view SQL database records from the live database, read & analyze all transactions like insert, delete, update, etc.
How to Check Transaction Log in SQL Server
You can easily find out the transaction for a particular user from LDF file with the help of SQL Log Analyzer tool. However, it works in both online and offline environment.
- Install & open SQL Log Analyzer tool.
- Then, click on the Open button to add .ldf file.
- Now, you will get two options:
- Online DB Option
- Offline DB Option
If you have selected Online database option, then you have to provide all server details.
- After that, the software will preview all transaction activities. From here, you will get the transaction log for a particular user.
Conclusion
In this write up, we have discussed an easy solution on how to read transaction logs in SQL Server. One can analyze and read all the transaction activities done in the logs by using this professional third party utility.
Procedure to Know How to Recover SUSPECT Database in SQL Server
MS SQL Server Database in SUSPECT mode is the pure sign that you cannot utilize the database or no any transactions are performed likely until it is back to online. You may have gone through a situation that your database is marked as SUSPECT. It occurs due to various reasons such as:
- Corruption in Transactional log file
- Defective Hardware
- Missing Transaction log file of database
- Virus attack
- Improper SQL Server shutdown
Look at the error log of your MS SQL Server to know the exact cause for your database. Error log expresses the precise cause why and how to recover SUSPECT Database in SQL Server 2000 / 2005 / 2008r2 / 2012 / 2014.
SysTools SQL Database Repair is one of the best solutions for quandaries of MS SQL Server. The software is programmed in such a way that it recovers and repairs the complete database from SUSPECT mode in exact form. The application is 100 % safe as well as secure to execute the recovery process.
Reasons of SUSPECT Database in SQL Server 2000 /2005 / 2008r2 /2012 /2014
- The drive is unapproachable to the server where SQL server log files are preserved. This can be initiated by corruption in hard drive partition.
- Occasionally, viral attack or malicious malware may be erasing server files. These files are missing as well as cannot be found. There can be various causes for the database in Suspect mode.
- Another cause for SUSPECT mode is because of recent MS SQL server crash in the middle of a transaction. If the transaction is updating values for large database and it is still midway, some changes made to some value and not others. It causes corruption in transaction logs and errors are made.
- Other application may be controlled by the server’s device files that are avoiding access to data files. These consequences in matters while opening SQL server database and connecting to it. Various antivirus blocks some files, which they think problematic. There may be corruption in the file, which is caused by malware that scanners start to repair as well as fix.
- Other reasons for the suspect database are corruption in transaction log instigated due to an incorrect shutdown of the local system.
Techniques to Repair Database from Suspect Mode in SQL Server
In a manner to determine this issue, it needs to change the status of the database to EMERGENCY mode, which gives read-only access to the administrator. The main determination of altering the mode of the database to the emergency is troubleshooting. For altering the status of database to EMERGENCY mode, execute the below T-SQL query:
ALTER DATABASE dbName SET EMERGENCY
Once you have altered database status now, the administrator can utilize it. Another step is the execution of DBCC CHECKDB. It checks all logical as well as the physical integrity of the stated database. If it catches any issue with the database then, there are some suggested repair options, i.e. repair_fast, repair_rebuild, and repair_allow_data_loss.
DBCC CHECKDB (‘dbName’)
Run T-SQL query to simply rollback any transaction as well as bring SQL database into Single User mode.
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Now, again execute DBCC CHECKDB with repair options to recover the SUSPECT database.
DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)
If you discover that script runs effectively, bring the database back from the mode of single-user to multi-user mode. Run the command to do it.
Note: By using DBCC CHECKDB with repair_allow_data_loss might recover database from the suspect mode in SQL Server. However, it causes data loss from the database.
Final Words
SQL Server is mainly utilized for database management to save as well as retrieve data. There are various errors as well as issues come across while using a database server. Some of them may be corrected by using manual methods, other may not. Out of which, one issue is Suspect Mode. Some advanced SQL users find it difficult to utilize data or connect to the database once it enters into suspect mode. After understanding this, we have discussed and answered the main user’s query i.e. how to recover SUSPECT database in SQL Server.
SQL Server Error 3117: Restore Database is Terminating Abnormally
SQL Server being a largely used relational database management system regularly encounters issues resulting in errors. One such errors, is the “restore database is terminating abnormally SQL error 3117” which has been causing a lot of trouble for the SQL Server users worldwide. The segment talks about the error as well as the reason due to which it occurs. Moreover, the workaround to overcome the consequences of this error have been discussed in the form of suggestions regarding the restore procedure and state to be used.
The Perpetrators of Microsoft SQL Server Error 3117
When the transaction log backup is tried, being restored to the same database it has been taken from, this error has high chances of taking place. SQL error 3117 clearly states that ‘no files are ready to rollforward’. This happens because; the log file that you are trying to restore is already available in the database. You can take the help of third party tool to view SQL transaction log.
TIP: In order to carry out the restoration process your way, you will have to perform the restoration of full backup of the database that had been taken right before the transaction log backup.
Recovery vs. NoRecovery State: Difference in Restoration
NORECOVERY state is to ensure that rollback does not take place. This state continues the roll forward along with the following statement mentioned in the sequence.
The restore sequence in this case helps restore other backups and then roll them forward.
RECOVERY (default) is the state that indicates that a rollback must only be performed once the roll forward has completed for the backup currently being restored.
Database recovery procedure demands the entire set of data that is to be restored (roll forward set) is in a consistent state with the database. The Database Engine issues an error 3117 in a condition where the data set is not rolled forward despite when it was meant to be to a state far enough to make it consistent enough with the database while RECOVERY is specified.
NOTE: In a condition like the one discussed above, one needs to make sure that the target database is not in a functional mode while the differential backup is being restored as this act can interrupt the procedure.
Restoring Differential Backup Post Full Backup Restore
To avoid the situation of coming across Microsoft SQL Server error 3117 would be to restore full backup with NORECOVERY and differential backup WITHRECOVERY.
RESTORE DATABASE DatabaseName FROM DISK = 'C:\DatabaseNameFull.bak' WITH NORECOVERY; RESTORE DATABASE DatabaseName FROM DISK = 'C:\DatabaseNameDiff.bak' WITH RECOVERY;
Conclusion
When queries are generated, they are executed manually. Therefore, the chances of failure become high due to a non-automated approach being made. Similarly, other technical failures tend to take place resulting in restore database is terminating abnormally Microsoft SQL Server error 3117. Technical glitches are common when working with big data concept because of the load, processing and mass data involved. However, dealing with the issue carefully by understanding the cause of its occurrence helps overcome it precisely without taking rounds. When dealing with the restore issue, most DBAs suggest NORECOVERY as the roll forward set to restore backup without a failure encountered. Drop in a comment just in case of further queries regarding the same issue in restoring SQL server database. A suitable solution will be shared via response in the soonest possible time to help deal with the situation at hand.
Troubleshooting Steps For Corrupt or Suspect Databases
Problem
Sometimes, while working on SQL Server, users receive various error messages like SQL Server Error 5172 due to encounter of suspect or corruption in databases. This happens because of operational mistakes or faulty hardware, which can be resolved by using the various actions for corrupt or suspect databases.
Solution for Corrupt or Suspect Database
There are some recommended actions for corruption or suspect databases that help users in handling a situation as discussed below. However, before using these actions users must ensure to have backup strategy that helps to recover the data from failures.
- ERRORLOG File
Firstly, check the ERRORLOG file for SQL Server to find the occurrence of an error. Many times, the data or log file is missing due to which, there are chances of suspect or corruption of databases. During the startup of SQL Server, it will help to encounter the problem.
- NO_INFOMSG Option
User can run the DBBCC CHECKDB against the databases to find the reasons of the occurrence of an error message by showing the error number. There might some specific recommendation for error message. This option is helpful as it return only error message such as:
DBCC CHECKDB (adventureworks) WITH NO_INFOMSGS
- RECOVERY_PENDING option
If the log file is missing then, user cannot run DBCC CHECKDB to perform last log backup. Instead of this, users can use SELECT name, state_desc, database_id from databases. It will help them to more to understand the problem. With this, they can see RECOVERY_PENDING if any database is missing or hindering in SQL Server from executing automatic recovery at startup. It will show all the status for database.
- Hardware Diagnostics
To remove the occurrence of an error, users can run check errorlog, eventlog. If still the error occurs then, they can perform the hardware diagnostics. It will help to resolve the corruption of database issue. If there is some problem detected then, ensure the replacement of faulty hardware.
- Tail Log Backup
The tail log backup contains the transaction log backup, which is backed up most recently. It helps to recover right up to the point of disaster. It makes easy for users as they get all those log records back that was corrupted.
- Repair Command
If the database is not suspected then, run DBCC CHECKDB with REPAIR command. It is a secondary option because it will result in data loss. This command will repair and solve the issue or it will result in loss of whole data.
- Enable Checksum
In a way to detect the corruption issue, users can enable the CHECKSUM verification. It helps them to give the timely alerts when any error is occurred because SQL Server is a robust to check for other disk errors.
Tip:
- Never detach the suspect database otherwise, all chances are lost to have backup.
- Depending upon the error, manually rebuild non-cluster indexes, drop, and reload table if data is static.
Conclusion
The recommended actions for corrupt or suspect databases help to bring back the data in accessible mode. However, users cannot repair a database of SQL Server in case of unavailable or not updated backup.
Help! I have -2, -3, or -4 Session ID!
We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error:
Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.
In order to kill the session ID, you need to find the unit of work (UOW) guid.
SELECT DISTINCT(request_owner_guid) AS UOW FROM sys.dm_tran_locks WHERE request_session_id IN (-2,-3,-4)
Now you can kill this using UOW:
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.
The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?
| Session ID | Description |
|---|---|
| -2 | The blocking resource is owned by an orphaned distributed transaction. |
| -3 | The blocking resource is owned by a deferred recovery transaction. |
| -4 | Session ID of the blocking latch owner could not be determined due to internal latch state transitions. |
Reference: Books Online, sys.sysprocesses (Transact-SQL)
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
Issues with CU6 for SQL Server 2014 SP1
Microsoft released an update to CU6 (link) for SQL Server 2014 SP1; this update was to address an issue where even having NOLOCK hint in queries was leading to blocking and deadlocks in the default SQL Server lock-based isolation level or high levels. From the KB article:
Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, and specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries that try to access SourceTable will be blocked.
While one transaction is holding an exclusive lock on an object (for example, an ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable by using the NOLOCK hint. In this scenario, the SELECT query that is trying to access SourceTable will be blocked.
Reference: CU6 for SQL 2014 SP1 (Deprecated) [Link].
Therefore if you have the older build installed (12.0.4449) please update to newer build (12.0.4457).
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
Dissecting SQL Server Execution Plans
I remember my days before, Microsoft SQL Server PFE. I wanted to learn everything and know everything about SQL Server. However, getting hold of good resources was tough, as I didn’t have any mentor when I started down my journey to becoming a SQL Server Database Administrator.
Along the way I did pick up lots of books and references. One of such books is Dissecting SQL Server Execution Plans.
I read this book before becoming PFE, I read this now, and I recommend everyone read this book more then once.
Grant Fritchey (Blog|Twitter) wrote the book back in 2008; I would still recommend take ready. This book will only help you be better DBA and Developer.
SQL Central, Jeff Moden, Dissecting SQL Server Execution Plans
http://www.sqlservercentral.com/articles/Book+Reviews/69019/
Amazon, SQL Server Execution Plans
SQL Central, Red Gate, EBook
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
