Share via

Need automation script to restore multiple databases at a time with overwrite and no recovery option

Sai 126 Reputation points
2021-07-30T16:30:13.473+00:00

Hi Team,

We are trying to restore FULL and T-log databases with overwrite and No recovery options from a backup folder. Can someone help me with the script for automation.

We want to join these databases to AG. It is taking 5 hours time manually.
Please help me.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,362 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
Accepted answer
  1. CathyJi-MSFT 21,136 Reputation points Microsoft Vendor
    2021-08-03T01:33:59.827+00:00

    Hi @Sai ,

    > Is it recommended adding 40 databases (5TB data) in one availability group or can I divide 40 databases into multiple Availability groups

    > Could you please share any blog for this.

    Update my above reply. It depends on your choice.

    An Availability Group is a unit of failover. Therefore, when considering how to group databases into an AG, keep in mind that all of these DBs will fail over together. If there are multiple databases that must failover together, then I would recommend putting those databases into a single AG.

    Similarly, you may have databases that are completely independent, where you want to put them in separate AGs so that a failover of one does not affect the other.
    In some cases, you may wish to put databases into AGs based on logical groups that fail over together, even if they technically can fail over independently.

    Having too many AGs or too many DBs per AG can both be management headaches. For example:

    • If you have 50 DB, each in it's own AG: you'll have 50 unique connection strings to manage, and 50 failovers to execute for a planned failover.
    • If you have 50 DBs in a single AG: If there is a problem with one database that necessitates a failover, then the other 49 databases need to be failed over as well, with all 50 databases incurring a short outage during the failover.

    Quote from an old thread.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,186 Reputation points
    2021-07-30T21:00:47.627+00:00
    0 comments
  2. CathyJi-MSFT 21,136 Reputation points Microsoft Vendor
    2021-08-02T07:39:39.547+00:00

    Hi @Sai ,

    If the databases that will be added to the SQL AG are not too big, suggest you using automatic seeding option. If the databases are big, suggest you using join only option. So your environment databases are big or not?

    For scripts to restore multi databases, please check if below blog could help you.

    How to get script of backup and restore of multiple databases

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

    0 comments
  3. Sai 126 Reputation points
    2021-08-02T08:30:06.967+00:00

    We have around 5TB Data in 40 databases. Going to restore 40 databases Full and T-log backups in new nodes.

    If I select Automatic seeding It will take long time so we want to restore it with norecovery manually or automatically and want to join it to availability group.

    Is it recommended adding 40 databases (5TB data) in one availability group or can I divide 40 databases into multiple Availability groups.

    Please suggest.

    0 comments
  4. CathyJi-MSFT 21,136 Reputation points Microsoft Vendor
    2021-08-02T09:09:43.627+00:00

    Hi @Sai ,

    > Is it recommended adding 40 databases (5TB data) in one availability group or can I divide 40 databases into multiple Availability groups.

    No, suggest you dividing 40 databases into multiple Availability groups.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.