Backup All Databases

				
--**************************************
-- Name: Backup All Databases
-- Description:This script was written because I have a server with about 115 client databases on it. It was a rather hard task to try to backup all of them by hand and I didnt want to spend a few grand on a good piece of backup software. Basicly, this script just loops thru all the databases on a given server and backs them up somewhere on that machine. From there, you can compress them and transfer them off site, etc. The backup will include MSDB and Master databases for complete 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: PHendryx
--
-- Returns:If you run this on a schedule, you dont see anything, but if you run it in the query analyzer, you will see different stats about each database as its 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/vb/scripts/ShowCode.asp?txtCodeId=284&lngWId=5--for details.--**************************************

--*****************************************************************************
--Developed By: Paul Hendryx (paulh@tekker.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 experienced 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 should backup. Northwind is just an example 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