SQL Server with Mr. Denny

Mar 5 2019   9:19PM GMT

Recovering SQL Server Databases from a Failed SAN Drive

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:

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

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • alexf01
    Quite a horror story. Imagine what would've happened had there *were* transactions in process...
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: