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.

Share on Facebook

Post on X

Follow us

Save