Backup all databases

• klnsuddu

  SSCertifiable

  Points: 5836

  More actions

  February 23, 2009 at 1:55 pm

  #131098

  Hi Gurus,

  Iam using this procedure to backup all databases. Can u suggest me the script to add to verify when backup finished.

  USE [master]

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  create procedure [dbo].[Backup_DB]

  As

  DECLARE @name VARCHAR(75) -- database name

  DECLARE @path VARCHAR(256) -- path for backup files

  DECLARE @fileName VARCHAR(256) -- filename for backup

  DECLARE @fileDate VARCHAR(20) -- used for file name

  SET @path = '\\Server1\BACKUP$\Ins1\'

  SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

  DECLARE db_cursor CURSOR FOR

  SELECT name

  FROM master.dbo.sysdatabases

  WHERE name NOT IN ('tempdb')

  OPEN db_cursor

  FETCH NEXT FROM db_cursor INTO @name

  WHILE @@FETCH_STATUS = 0

  BEGIN

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

  BACKUP DATABASE @name TO DISK = @fileName

  FETCH NEXT FROM db_cursor INTO @name

  END

  CLOSE db_cursor

  DEALLOCATE db_cursor

• Sailor

  SSCertifiable

  Points: 5246

  More actions

  February 23, 2009 at 2:04 pm

  #948967

  Check out RESTORE VERIFYONLY in Books Online.

• maechismo_8514

  SSCoach

  Points: 17510

  More actions

  February 23, 2009 at 2:17 pm

  #948983

  The only best way to verify a good backup is to restore it to a different server but there are alternatives as well like said abive you can look at different options. check out RESTORE VERIFY ONLY