Stored Procedure to Backup all Databases

  • Wesley Brown

    SSChampion

    Points: 12035

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

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Manish_

    Ten Centuries

    Points: 1070

    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

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

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