SQL SERVER – Finding Location of Log File when Primary Datafile is Crashed

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 (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Finding Location of Log File when Primary Datafile is Crashed

  1. Great question Pinal, here’s my response.

    http://www.the-fays.net/blog/?p=69

    In summary, if SQL Server service comes online, you must perform a backup of the LOG file.
    Then restore the full backup of the database specifying WITH NORECOVERY to leave the database in RECOVERING mode and ready for you to apply the log backups.
    Then restore the log backup from the first step specifying WITH RECOVERY to apply the tail of the log, then switch the database to ONLINE mode.

  2. Yes, Jason beat me to the finish with his quick answer. :)

    I would like to add a few more things, though:
    1. depending on how smart your database design is, you might have chosen to use multiple filegroups and files for your database, and when a problem occurs, maybe there is only a single filegroup which is not available. In that case, we can do a piecemeal restore with norecovery and then restore the tail of the log with recovery, which brings the database automatically online.
    2. It gets even better in enterprise edition, since we get an online filegroup restore. This means that if we have different filegroups, and our database is unavailable, we can choose to bring the most important filegroups first and let the users work with their data while we restore the rest of the filegroups.

    Once again, the scenarios above really depend on how flexible the database design is.

    Feodor

  3. Jason

    Pinal says that >>

    As the database is not online and not accessible, we cannot take backup of the log file using

    BACKUP LOG DatabaseName TO DISK=”location”.

    b.But in your post you defend the statement saying >>

    Stopped SQL Server Service
    Deleted the .mdf file
    Started SQL Server Service.
    FullBackupTest failed to come online again.
    In Management Studio I executed
    BACKUP LOG FullBackupTest TO DISK=’FullBackupTest.trn’ WITH NO_TRUNCATE”

    am i taking it wrong ,please correct me

  4. Apply only if there is no backup to recover corrupted data,
    1) Take database in emergency mode
    2) Take database in single user mode with rollback immediate
    3) run dbcc checkdb(dbname,repair_allow_data_loss) to cure your database
    4) bring it online and access your database with multi_user

    hope I got your question correctly

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s