Post reply

Backup all databases

  • 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

  • February 23, 2009 at 2:04 pm

    #948967

    Check out RESTORE VERIFYONLY in Books Online.

  • 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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply