Recovering SQL Server Databases from a Failed SAN Drive

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.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?