My friend and SQL Expert Vinod Kumar asked a very interesting question in his latest blog post.
Quick Quiz:Do you need the primary data file available to backup your transaction log after a crash?
This question can have multiple answers. While he asked the question on blog, I was sitting very next to him and he asked what do I think about it. We had less than 10 minutes during the lunch break after which we had to get back on work.
To simulate Primary Datafile is corrupted (again please note – this is just a quick exercise and not real corruption exercise), I have stopped SQL Server and moved the database to another location.
Primary Data File is corrupted
NOTE: Do not practice this on the production server. Avoid using it on the development server if it is used for any other purpose. In fact, skip reading the session, learn about recovery and not about corruption.
When I restarted the SQL Server, the database did not come online. I quickly checked the error logs and I found the entry of the data file location.
Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testdb.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
Alternatively, you can open the .mdf file in notepad and remove some part of the file. When you restart the SQL Server, it will not bring the database online. When I checked the error log, Â I found the following errors.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x58015801; actual: 0x0f1eabcb). It occurred during a read of page (1:0) in database ID 17 at offset 0000000000000000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testdb.mdf’.
I can easily assume that my log file will be placed along with the datafile, and it will be in the same folder. However, there may be the cases when this is not true. Log file can be anywhere and knowing the location of the same is very important.
As the database is not online and not accessible, we cannot take backup of the log file using
BACKUP LOG DatabaseName TO DISK=”location”.
I quickly searched on the Internet and I ended up on a very interesting blog post written by me SQL SERVER – Find Current Location of Data and Log File of All the Database. I have previously written how I can find the location of all the files of the database.
SELECT name, physical_nameAS current_file_location
FROM sys.master_files
The above query will give me the location of the log file. I can go there and take a copy of the log file.
Now, here is the next question to continue this thread.
Here is the situation:
Let us assume that you have corrupted (beyond repairable) or missing MDF. Along with that you have full backup of the database at TimeA. Additionally there has been no backup since TimeA. You have now recovered log file at TimeB. How to get the Database back online with the same state as TimeB?
Link back to this blog with your answer.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
