How to Backup All Databases in SQL Server

Hi friends,in this article I will explain about How to Backup All Databases in SQL Server.

Below  is a SQL script to get all Databases Backup in specified folder.
Backup file name will be like "DataBaseName_{Date}-{Month}-{Year}.bak"

DECLARE @DBName VARCHAR(MAX)  DECLARE @DBBackupFileName VARCHAR(MAX)  DECLARE @FileDate VARCHAR(20)  DECLARE @Path VARCHAR(MAX)  SET @Path='D:\DBBackup\'  SET @FileDate=CONVERT(VARCHAR(20),GETDATE(),105)  DECLARE dbCursor CURSOR FOR  SELECT name  FROM master.dbo.sysdatabases  WHERE name NOT IN ('master','tempdb','model','msdb')  OPEN dbCursor  FETCH NEXT FROM dbCursor into @DBName  WHILE @@FETCH_STATUS = 0  BEGIN  SET @DBBackupFileName= @Path + @DBName + '_' + @FileDate + '.bak'  BACKUP DATABASE @DBName TO DISK =@DBBackupFileName  FETCH NEXT FROM dbCursor into @DBName  END  CLOSE dbCursor  DEALLOCATE dbCursor

I have 3 databases.So,the output of the above code as shown in the below figure.