Stored Procedure to Backup all Databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Wesley Brown » Stored Procedure to Backup all Databases

Stored Procedure to Backup all Databases

Rate Topic

Display Mode

Topic Options

Author

Message

Wesley Brown

Posted Tuesday, September 25, 2007 2:38 PM

SSChasing Mays

Group: Moderators
Last Login: Tuesday, May 07, 2013 3:54 PM
Points: 608, Visits: 379

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

Post #402749

Manish_

Posted Thursday, June 18, 2009 11:50 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 01, 2013 10:14 AM
Points: 286, Visits: 252

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

Post #738064

« Prev Topic | Next Topic »

Permissions