Sql statement for databases in restoring state

Avyayah 1,291 Reputation points
2024-01-16T23:20:08.5066667+00:00

Few databases are replicating to another server and are in restoring state. While replicating all databases were chosen as no recovery so all differential and transactional logs can be applied. To restore and bring the database online so that users can connect we have planning to use this statement: RESTORE DATABASE [test]  WITH RECOVERY GO This should restore the database to current state with all transactional logs applied and application can connect to the database.

SQL Server Other
0 comments No comments
{count} votes

Accepted answer
  1. RahulRandive 10,486 Reputation points Volunteer Moderator
    2024-01-17T00:00:54.0033333+00:00

    Hi Avyayah, The approach you have described is generally correct for restoring a database that and make it online/available.

    When restoring a database, it is common to set the database to "no recovery" mode so that all differential and transactional logs can be applied. This allows the restoring database to stay in sync with the source database.

    To restore the database and bring it online so that users can connect, you can use the RESTORE DATABASE statement with the WITH RECOVERY option. This will restore the database to the current state with all transactional logs applied, and bring the database online so that users can connect.

    However, before executing the RESTORE DATABASE statement, it is important to ensure that all necessary transactional logs have been applied to the database. You can check the replication/log shipping (generally use) status to ensure that all logs have been applied before executing the RESTORE DATABASE statement.

    Also, keep in mind that executing the RESTORE DATABASE statement with the WITH RECOVERY option will bring the database online and make it available for users to connect. If you need to apply additional transactional logs or make other changes to the database, you may need to take the database offline again before making those changes.

    Thank you!

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.