Monday, April 21, 2014

How to make mackup for all database?

CREATE PROCEDURE dbo.BackUpAllDatabases
( @BackUp_Path VARCHAR(300) )
AS/* -- Comments -- *Created and Licenced to dotnetbites.comThis procedure would backup all the databases that are online to the
@BackUp_Path specified by the developer or DBA*/
BEGIN
DECLARE @DBName VARCHAR(100)
, @fileName VARCHAR(256)
, @Count INT
, @Index INT
DECLARE @Databases TABLE(ID INT IDENTITY, DBName VARCHAR(100)) INSERT INTO @Databases(DBName)
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND DATABASEPROPERTYEX ([Name],'Status') = 'online'
SELECT @Index = 1, @Count = COUNT(*) FROM @Databases
WHILE @Index <= @Count
BEGIN
SELECT @DBName = DBName FROM @Databases
WHERE ID = @Index
SET @fileName = @BackUp_Path + '/' + @DBName + '_'
SET @fileName =  @fileName + CONVERT(VARCHAR(20),GETDATE(),112) + '.BAK'
BACKUP DATABASE @DBName TO DISK = @fileName
SET @Index = @Index + 1
END
END





exec  BackUpAllDatabases 'D:\bbbb'

No comments:

Post a Comment