Backup & Restore Multiple Databases

Spread the love

Backup & Restore Script for Multiple Databases

Backup and restore all databases from an old SQL server and restore it on new SQL server. The number of databases are approx 99+. Let’s try to make backup and restore scripts of all databases.

Note: Here backup and restore server are defferent. Before executing restore script on target server, default path for restoration must be set in the target server.


By using Query 1 we can generate the backup script of all database from source server. Then we need to copy the result of the Query 1 and run the same on the source server to perform the actual backup activity. Once backups are ready, then we need to generate the restore script. To generate the restoration script we need to execute the Query 2 on the source server. Once the restoration script is ready, further we can execute the final restoration query on target server.

Backup Script

Execute the below script on source SQL server. Change the backup folder location as per your requirement.

Query 1


To run a script on a Microsoft SQL Server using SSMS.

  • Open Microsoft SQL Server Management Studio
  • Select [New Query] from the toolbar
  • Copy the ‘Example Query’
  • Select the database to run the query against
  • Paste the ‘Example Query’ into the query window
  • Click the [Execute] button

As a result, we will get a script.

Result 1

Now we need to execute the Result 1 on the same source SQL Server to perform the backup process.

Result 1.1

Backup file generated at the provided location

Result 1.2

Restore Script

Now execute the below Query 2 script on source SQL Server, remember we have to use the backup folder location in the script.

Query 2

Result 2


Now we need to execute the Result 2 on the target SQL Server to perform the actual restore activity.

Enjoy !! 🙂

Spread the love
Author: OTi
Meet OTi, the brains behind the popular tech blog "" OTi is an accomplished author, tech enthusiast, and an expert in SQL Server. With over a decade of experience in the tech industry, he has become a trusted voice in the world of database management. OTi's passion for technology began at a young age, and he has been tinkering with computers ever since. He graduated with a degree in Computer Science and has since worked for some of the biggest tech companies in the world. His experience has given him a deep understanding of how technology works and how it can be used to improve our lives.

Leave a Reply

Your email address will not be published. Required fields are marked *