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://blog.sqlauthority.com)
7 Comments. Leave new
Great question Pinal, here’s my response.
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.
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
Hope we cant restore the log file, as there will be an LSN mismatch. We have bring the DB online with Time A backup.
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
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
John,
Jason is correct, You could initiate a log backup if the data file of the database is missing. If the transaction-log file is not damaged and if you have the database in Full recovery model with an earlier full backup, you can initiate a log backup for the database. I have created a script to test the same, you can find it here: http: // sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/
Thank you ,Sudeepta that was very helpful.