Restoring SQL Server databases from Pending State

Today, after migrate a SQL Server 2012 server we faced a situation where all databases were in Pending State.

Browsing the web we found the commands needed to put databases back to online state again, the only issue was that this server has about sixteen databases and execute the script sixteen times, changing variables would be time consuming and we could type something wrong. So I wrote this very simple script that iterates over a cursor returning databases name which is in Pending State, Suspect and Single User mode.

Hope that it saves you some work hours as it saved me. :)

declare @dbname varchar(255); DECLARE dbname_cursor CURSOR FOR SELECT name--, database_id, create_date, STATE_DESC FROM sys.databases WHERE STATE_DESC in ('SINGLE_USER', 'SUSPECT', 'RECOVERY_PENDING'); OPEN dbname_cursor FETCH NEXT FROM dbname_cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Recovering "' + @dbname + '"...' exec sp_resetstatus @dbname; exec('alter database [' + @dbname + '] set emergency'); dbcc checkdb(@dbname) dbcc checkdb(@dbname) exec('alter database [' + @dbname + '] set single_user with rollback immediate'); dbcc checkdb (@dbname, repair_allow_data_loss) exec('alter database [' + @dbname + '] set multi_user'); dbcc checkdb (@dbname) FETCH NEXT FROM dbname_cursor INTO @dbname END CLOSE dbname_cursor; DEALLOCATE dbname_cursor;

Marcus Rehm

Software development, diapers and bottles...

Restoring SQL Server Databases From Pending State

Comments