Change all databases in "RESTORING" state to ONLINE

Question

I have multiple databases that I have a backup script that pulls in logs from another server and restores them. When restoring I leave the database in the RESTORING state so further transaction logs can be restored. I am trying to find a solution where all databases left in the RESTORING state can be set to ONLINE. I know how to do this for a single database, just not for multiple dbs. Can anyone lend a hand?

What I have thus far are two commands, I am just not sure how to combine them.

SELECT name FROM sys.databases WHERE state_desc = 'RESTORING"

and

RESTORE DATABASE "name" WITH RECOVERY

Answer 1

You need to use the WITH RECOVERY option, this seems likely caused by the restore script adding the WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. With your database RESTORE command, to bring your database online as part of the restore process. This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

**RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE, RECOVERY, STATS = 10**

You may have more success using the restore database wizard in SQL Server Management Studio (SSMS). This way you can select the specific file locations, the overwrite option, and the WITH Recovery option. Sometimes, The restoration process stuck just because of size of the database file.

You can either Use Below one:

How to Change the state from RESTORING To ONLINE

The following will go through each of the lines returned by the query building a RESTORE DATABASE statement around the name, using WITH RECOVERY at the end. The statement is then shown in the messages window which you can then use as needed. We'll make use of the state value to restrict the statement to only altering those that are not at state 1.

DECLARE @SetWithRecovery nvarchar(max) = ''

SELECT @SetWithRecovery += 'RESTORE DATABASE ' + quotename(Name) + ' WITH RECOVERY; ' + Char(10)
FROM sys.databases
WHERE [State] = 1

PRINT @SetWithRecovery

--If you wish to execute it as well

EXEC (@SetWithRecovery)

When you execute the code you'll receive a message for each restore statement showing the processing information.

Before you do this, please make sure you understand the options what you has to do, if not it may cause data loss.

Check this link if needed: https://devondba.blogspot.com/2014/11/how-to-change-sql-database-from.html

Answer 2

Assuming you're looking for an automation solution, the code below should work. Yes, it uses a cursor. I know they're incredibly evil; however, it's simple to follow, and (in this particular case) it seems reasonable to assume that we won't be dealing with millions of rows.

USE master;
GO

DECLARE c_DB CURSOR LOCAL STATIC FOR
SELECT name
  FROM sys.databases
 WHERE state_desc = 'RESTORING'
;
DECLARE @dbName NVARCHAR(128)
       ,@stmt NVARCHAR(max)
;

OPEN c_DB
FETCH NEXT FROM c_DB INTO @dbName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
  SET @stmt = 'RESTORE DATABASE ' + QUOTENAME(@dbName) + ' WITH RECOVERY;'
  --PRINT @stmt;
  EXECUTE sp_executesql @stmt;
  FETCH NEXT FROM c_DB INTO @dbName;
END;

CLOSE c_DB;
DEALLOCATE c_DB;

Answer 3

You could execute the output of the below command.

SELECT 'restore database ' + name + ' with recovery' FROM sys.databases WHERE state_desc = 'RESTORING'