How to Backup All Databases in SQL Server

This is a SQL script of get all Databases Backup in particular folder.

This is a SQL script of get all Databases Backup in particular folder.
backup file name will be "DataBaseName_{Month}_{Date}_{Year}.bak"

Notes:

1. Current user have full access of backup database.
2. Remote drive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive.
3. You have to change you sql server account to a network account and add that user to have full access to the network drive you are backing up to.

SQL Script

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