65.9K
CodeProject is changing. Read more.
Home

Backup all databases on server

starIconstarIconstarIconstarIconemptyStarIcon

4.00/5 (2 votes)

Jul 15, 2010

CPOL
viewsIcon

10735

T-SQL scripts: Backup all databases on server with compress feature of SQL Server 2008

USE MASTER
GO
-- Compress feature of SQL 2008
EXEC SP_CONFIGURE 'BACKUP COMPRESSION DEFAULT', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
 
DECLARE @DATABASENAME SYSNAME
DECLARE @SQLCOMMAND VARCHAR(1024)
DECLARE CURDBNAME CURSOR FOR
SELECT [NAME] FROM MASTER..SYSDATABASES
WHERE [NAME] NOT IN ('TEMPDB')
 
OPEN CURDBNAME
FETCH CURDBNAME INTO @DATABASENAME
-- Foreach database
WHILE (@@FETCH_STATUS = 0)
 
BEGIN
	IF DATABASEPROPERTYEX (@DATABASENAME,'STATUS') = 'ONLINE'
	BEGIN
		SELECT @SQLCOMMAND = 'BACKUP DATABASE [' + @DATABASENAME +
		'] TO DISK = ''D:\BACKUPS\DATABASES\' + @DATABASENAME + '.BAK'' WITH FORMAT'
		EXECUTE (@SQLCOMMAND)
		PRINT @DATABASENAME + ' BACKUP COMPLETED!'
	END
	FETCH CURDBNAME INTO @DATABASENAME
END
 
CLOSE CURDBNAME
DEALLOCATE CURDBNAME