Sql-server – Change all databases in “RESTORING” state to ONLINE

restoresql servert-sql

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

Best Answer

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

Related Question