In this blog post, I will brief you on how to back-up all the databases in the MS SQL Server at once, using an SQL Stored Procedure.
Many developers who work with MS SQL Server face this problem when they try to migrate the databases to a new server from an old one. SQL Server has no option to back-up all the databases in the current instance, at once. I had to face the same problem and that drove me to find a solution.
After checking many online forums I finally managed to develop a stored procedure to run in the server instance. It will automatically backup all the databases you have in the server to the location provided by you.
This is the stored procedure you have to use.
This is a normal stored procedure; it can be understood easily. Remember that you have to change a certain line before this is run on your SQL Server Management Studio.
See line number 17. That is where the backups will be stored. You must give the folder path in which you are going to save the backups in your server/machine. Simply replace the first part that starts with “C:\”, with the path to your folder. It will create the backups with the database names in that directory.
If you have many databases, this process will take a longer time to be completed. However, at the end, you will have all the databases backed-up, in the selected directory with ease.