Recovering SQL Server Databases from a Failed SAN Drive

Published On: 2019-03-05By:

I was recently cleaning the house when I got a phone call from a client that I hadn’t heard from in a while.  Their SAN had decided that it was going to corrupt one of the LUNs. This LUN happened to be the Log drive on one of their SQL Servers.

Needless to say, SQL was down.

We created the LUN, and created the folders on the drive, and started SQL.  All the databases on the server showed “Recovery Pending” as their state in Management Studio.

Manually bringing the databases online wasn’t going to work as that caused error 5181 which reads:

Could not restart database MyDatabase. Reverting to the previous status.

Apparently, that wasn’t going to work. I was going to need a bigger hammer, in this case, a much bigger hammer. That bigger hammer was emergency mode and checkdb.

The users would be back on the system in about 8 hours, and the users, in this case, were lawyers, some of who might need to be in court in the morning, and the system was down wasn’t an excuse that was going to fly in front of a judge.

Since I had 169 databases to bring online, a script would be needed to handle this.  That script was pretty rough, but it got the job done.  Thankfully no users would be using the database, so there “should” be no transactions that were in processes. We wouldn’t be able to recover them anyway.

declare @name varchar(max)
declare @sql varchar(max)
declare cur CURSOR for select name from sys.databases where state = 3
open cur
fetch next from cur into @name
while @@FETCH_STATUS = 0
begin
set @sql = 'alter database ' + @name + ' set emergency, single_user
dbcc checkdb (''' + @name + ''', repair_allow_data_loss)
alter database ' + @name + ' set online, multi_user'
exec (@sql)
fetch next from cur into @name
end
close cur
deallocate cur

The script ran, all the databases came online (it took a while to run, the databases on the server are about 9 TB in size) and all was well.

Denny

The post Recovering SQL Server Databases from a Failed SAN Drive appeared first on SQL Server with Mr. Denny.

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via