SQL Server – Backup all the sql server databases

Sharing with you script to backup all the SQL server databases


Script:


DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName NVARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘D:\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113)
SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,'model’,'msdb’,'tempdb’)

–according to requirement we can filter databases in where clause

SET ROWCOUNT 1

WHILE (exists(SELECT * FROM #temp WHERE flag=0))

BEGIN

SELECT @name=name FROM #temp WHERE flag=0
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH INIT
UPDATE #temp SET flag=1 WHERE flag=0

END

SET ROWCOUNT 0

DROP TABLE #temp

if anybody have any other idea please share.