question

Sai-0641 avatar image
0 Votes"
Sai-0641 asked Cathyji-msft edited

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

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Since a restore for an AG is a one-time operation, why would you need to automate it? Further - you can now use automatic seeding to add databases and SQL Server will take care of the rest, so a restore isn't necessary. If the restore is necessary because the databases are too large then you definitely do not want to automate that process - as there are going to be additional steps you need to take to ensure the database can be added.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @Sai-0641,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @Sai-0641,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sai-0641 avatar image
0 Votes"
Sai-0641 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Sai-0641 commented

Hi @Sai-0641,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your reply!

Could you please share any blog for this.

0 Votes 0 ·