--**************************************
--     
-- Name: Backup All Databases
-- Description:This script was written b
--     ecause I have a server with about 115 cl
--     ient databases on it. It was a rather ha
--     rd task to try to backup all of them by 
--     hand and I didnt want to spend a few gra
--     nd on a good piece of backup software. B
--     asicly, this script just loops thru all 
--     the databases on a given server and back
--     s them up somewhere on that machine. Fro
--     m there, you can compress them and trans
--     fer them off site, etc. The backup will 
--     include MSDB and Master databases for co
--     mplete disaster recovery.
This script is meant TO run daily. ON sundays, it will perform FULL backups OF each DATABASE AND on the other days it does a differential backup.
This script will NOT overwrite ANY files, it stores the backups IN the following format:
dbname_(full OR diff)_20010323.bak
ANY questions, please email me.
-- By: Paul Hendryx
--
-- Returns:If you run this on a schedule
--     , you dont see anything, but if you run 
--     it in the query analyzer, you will see d
--     ifferent stats about each database as it
--     s backing up.
--
-- Side Effects:None. Very low CPU usage
--     .
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/xq/ASP/txtCod
--     eId.284/lngWId.5/qx/vb/scripts/ShowCode.
--     htm--for details.--**************************************
--     

--**************************************
--     ***************************************
--Developed By: Paul Hendryx (paulh@tekk
--     er.com)
--Date: 3/20/01 3:21 PM
--Current Version: 1.0
--
--The only line that should be edited is
--      the @BackupDirectory line. The
--rest should only be modified by experi
--     enced professionals.
--**************************************
--     ***************************************
--Declare our variables
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @IsSunday bit
DECLARE @Name varchar(30), @MediaName varchar(30), @HasFullBackup bit, @BackupDirectory nvarchar(200)
--Set the backup directory.. dont forget
--      the trailing \ otherwise it wont work.
SET @BackupDirectory = 'c:\backup\'
--Loop thru all the databases that we sh
--     ould backup. Northwind is just an exampl
--     e db and tempdb and model arent needed.
DECLARE Database_Cursor cursor FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' and name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_Cursor INTO @DB
WHILE @@fetch_status = 0
    begin
    	--See IF its sunday.. 1=is sunday, 0=is NOT sunday
    	SET @IsSunday = 
    		CASE Datepart(dw, CURRENT_TIMESTAMP)
    			WHEN 1 THEN 1 ELSE 0
    		END
    	--Set SOME stuff so it looks pretty IN sql server
    	SET @Name = @DB + '( Daily BACKUP )'
    	SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
    	SET @BackupFile = @BackupDirectory + + @DB + '_' + 
    		CASE @IsSunday 
    			WHEN 3 THEN 'Full' ELSE 'Diff' 
    		END + '_' + 
    		CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    	SET @Description = 
    		CASE @IsSunday 
    			WHEN 1 THEN 'Normal' ELSE 'Differential' 
    		END + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    	--Check TO see IF this db has a FULL backup. If its the master, we have to do a full backup.
    	SET @HasFullBackup = 0
    	IF (select count(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 or @DB = 'master'
    	BEGIN
    		--This DATABASE has a FULL backup.
    		SET @HasFullBackup = 1
    		--If its sunday, THEN we do a FULL BACKUP anyways.
    		SET @BackupFile = @BackupDirectory + @DB + '_' + 
    			CASE @IsSunday 
    				WHEN 3 THEN 'Full' ELSE 'Diff' 
    			END + '_' + 
    			CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    		--Set SOME more pretty stuff FOR sql server.
    		SET @Description = 
    			CASE @IsSunday 
    				WHEN 1 THEN 'Full' ELSE 'Differential' 
    			END + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    	END	
    	ELSE
    	BEGIN
    		--This db does NOT have a FULL backup.
    		SET @HasFullBackup = 0
    		--Full BACKUP time.
    		SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + 
    			CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    		--Set SOME more pretty stuff FOR sql server.
    		SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    	END
    	--If it IS sunday, or the CURRENT db is master, or if this db has NOT had a FULL backup, the do a full backup.
    	IF @IsSunday = 1 or @DB = 'master' or @HasFullBackup = 0
    	BEGIN
    		BACKUP DATABASE @DB TO DISK = @BackupFile 
    		WITH NAME = @Name, DESCRIPTION = @Description , 
    		MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , 
    		STATS = 10
    	END
    	--This would be a daily differentail backup.
    	IF @IsSunday = 0 AND @DB <> 'master' and @HasFullBackup <> 0
    	BEGIN
    		BACKUP DATABASE @DB TO DISK = @BackupFile 
    		WITH DIFFERENTIAL, NAME = @Name, DESCRIPTION = @Description , 
    		MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , 
    		STATS = 10
    	END
    	--Print SOME pretty info so we can debug.
    	PRINT '------------------------------------------------------------------------'
    	PRINT ''
    	PRINT 'DB: ' + @DB
    	PRINT 'Name: ' + @Name
    	PRINT 'Media Name: ' + @MediaName
    	PRINT 'Backup File: ' + @BackupFile
    	PRINT 'Description: ' + @Description
    	PRINT 'Is Sunday (0 OR 1): ' + str(@IsSunday)
    	PRINT 'DB has FULL BACKUP (0 or 1): ' + str(@HasFullBackup)
    	PRINT ''
    	PRINT '------------------------------------------------------------------------'
    	PRINT '------------------------------------------------------------------------'
    	print''
    	FETCH next FROM Database_Cursor INTO @DB
end

CLOSE Database_Cursor
DEALLOCATE Database_Cursor