How to take MS SQL Database Backup All At once | How to take all Database Backup from MS SQL Server

MS SQL is the most used database nowadays, we may have or maybe be in future, faced the issue of database backup, database backup with one by one single database is quite simple, you just have to right-click then go to Task then select backup and follow the instructions.

But suppose you have 100s of databases that you need to backup  now one by one process is way difficult because you can't do the selection process every time to take the backup this will take years, 

So how to take backup then?

Use the below script to take any number of database backups in any MS SQL database.

DECLARE @path VARCHAR(500)

DECLARE @name VARCHAR(500)

DECLARE @filename VARCHAR(256)

-- 1. Setting the backup path

SET @path = 'E:\Database_Backup_11022021'  

-- 2. Defining cursor operations

 

DECLARE db_cursor CURSOR FOR  

SELECT name 

FROM master.dbo.sysdatabases 

WHERE name  NOT in   ('Master') 

--3. Initializing cursor operations

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   

BEGIN

-- 4. Defining the filename format

   

       SET @fileName = @path + @name + '' + '.BAK'  

       BACKUP DATABASE @name TO DISK = @fileName  

 

       FETCH NEXT FROM db_cursor INTO @name   

END   

CLOSE db_cursor   

DEALLOCATE db_cursor

end 

Do remember SET @path = 'E:\Database_Backup_11022021', this path is the Backup path where all the databases will get saved.