FDiri
1/17/2018 - 6:45 PM

backups all user databases in a database server

backups all user databases in a database server

ALTER PROCEDURE [dbo].[jobBackupAllDatabases] 

AS 

DECLARE @name VARCHAR(50) -- 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 = 'F:\DBBACKUP\'   

 

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

 

DECLARE db_cursor CURSOR FOR   

SELECT name FROM master.dbo.sysdatabases  

WHERE name NOT IN ('master','model','msdb','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