Backup All the Database in Sql Server

Backup All the Database in Sql Server

DECLARE @DataBaseName VARCHAR(MAX)
DECLARE @FileName VARCHAR(MAX)
DECLARE @FileDate VARCHAR(20)
DECLARE @Path VARCHAR(MAX)

--Set the Path Where You want to take the DataBase BackUp
SET @Path='D:\SQL DB\BACKUP\'
--Get the Date to append it in Backup File name
SET @FileDate=REPLACE((SELECT CONVERT(VARCHAR(20),GETDATE(),101)),'/','_')

DECLARE DataBaseCursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
--Exclude the System Database from Backing up
WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN DataBaseCursor
FETCH NEXT FROM DataBaseCursor into @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName= @Path + @DataBaseName + '_' + @FileDate + '.BAK'
--BackUp task happening here
BACKUP DATABASE @DataBaseName TO DISK = @FileName

FETCH NEXT FROM DataBaseCursor into @DataBaseName
END

CLOSE DataBaseCursor
DEALLOCATE DataBaseCursor

Advertisement