In this blog post, I will brief you on how to back-upall the databases in the MS SQL Server at once, using an SQL Stored Procedure.

Many developers who work with MS SQL Server face thisproblem 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 developa stored procedure to run in the server instance. It will automatically backupall the databases you have in the server to the location provided by you.

This is the stored procedure you have to use.

view rawSQL Server Database Backup hosted with ❤ by GitHub

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 SQLServer Management Studio.

See line number 17. That is where the backups will bestored. You must give the folder path in which you are going to save thebackups 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 thedatabase names in that directory.

If you have many databases, this process will take alonger time to be completed. However, at the end, you will have all thedatabases backed-up, in the selected directory with ease.

‍