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.
