Backup all databases on server





4.00/5 (2 votes)
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