Use the following query to take a backup of all databases in a SQL SERVER
use master
go
IF (OBJECT_ID('tempdb..#UserDbS')IS NOT NULL) DROP TABLE #UserDbS
SELECT NAME INTO #UserDbS FROM SYS.sysdatabases WHERE sid <> '0x01' AND NAME NOT IN ('master','tempdb','model','msdb')
DECLARE @DBBACKUPPATH NVARCHAR(MAX) = 'Z:\SQLServer\Backups\',
@BackedDate NVARCHAR(50) = '_'+REPLACE(CONVERT(CHAR(10), GETDATE(), 103), '/', '')
--SELECT * FROM #UserDbS
WHILE EXISTS (SELECT * FROM #UserDbS)
BEGIN
DECLARE @SQLCMD NVARCHAR(MAX),@DbName SYSNAME
SELECT TOP 1 @DbName = NAME FROM #UserDbS
SET @SQLCMD = 'BACKUP DATABASE ['+@DbName+'] TO DISK = N'''+@DBBACKUPPATH+@DbName+@BackedDate+'.bak'' WITH NOFORMAT, NOINIT, NAME = N'''+@DbName+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
PRINT @SQLCMD
EXEC (@SQLCMD)
DELETE #UserDbS WHERE name = @DbName
END
