Stored Procedure to Backup all Databases

• Wesley Brown

  SSChampion

  Points: 12045

  More actions

  September 25, 2007 at 2:38 pm

  #178953

  Comments posted to this topic are about the item Stored Procedure to Backup all Databases

• Manish_

  Ten Centuries

  Points: 1070

  More actions

  June 18, 2009 at 11:50 pm

  #1012185

  I am also having a small script to backup all my databases.....

  Use Master

  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(),112)

  SELECT name,flag=0 into #tempbackup FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

  set rowcount 1

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

  BEGIN

  Select @name=name from #tempbackup WHERE flag=0

  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

  print @fileName

  BACKUP DATABASE @name TO DISK = @fileName

  Update #tempbackup set flag=1 WHERE flag=0 and name=@name

  END

  set rowcount 0

  drop table #tempbackup

  - Manish