T-SQL Script to Backup all User Databases

-- cleanup history
declare @dt datetime 

-- get the current date/time, subtract 7 days.
set @dt = DATEADD(d, -7, getdate())

-- delete backup history
exec msdb.dbo.sp_delete_backuphistory @dt
-- delete job history
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@dt
-- delete log entries
EXECUTE msdb..sp_maintplan_delete_log null,null,@dt

--GO

-- Create the directories for the database backups
declare @strBackupPath nvarchar(500)
-- set the root backup folder.
set @strBackupPath = 'C:\SQL Data\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\'

DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @strBackupPath

-- delete all child folders
delete from @DirTree where depth <> 1

-- declare a cursor to iterate through our list of databases
-- retrieve only user databases, owner_sid <> 0x01
declare curDatabases cursor FAST_FORWARD for
	select [name]
	from sys.databases
	where owner_sid <> 0x01
OPEN curDatabases

-- declare a variable to hold the database name.
declare @dbName nvarchar(500) 

-- get the 1st record
fetch next from curDatabases into @dbName

WHILE @@FETCH_STATUS = 0
begin
	-- build the backup path for the current database
	declare @strDatabaseBackupPath nvarchar(500)
	set @strDatabaseBackupPath = @strBackupPath + @dbName
	
	-- determine if the subdirectory needs to be created.
	if (not exists(select 1 from @DirTree where subdirectory = @dbName))
	begin	
		-- create the subdirectory
		EXEC master.dbo.xp_create_subdir @strDatabaseBackupPath		
	end	

	-- delete the old backup files.
	EXECUTE master.dbo.xp_delete_file 0
		, @strDatabaseBackupPath
		, N'bak'
		, @dt

	-- declare a variable that we'll use to get the individual date parts.
	declare @lDatePart int
	-- get the current date/time
	declare @dtNow datetime
	set @dtNow = GETDATE()

	-- declare our variable to hold the backup date/time stamp string.
	declare @strBackupTimeStamp nvarchar(50)
	-- initialize the Backup date/time stamp string.
	set @strBackupTimeStamp = '_backup'
	-- build the rest of the date/time stamp string.
	-- year
	set @strBackupTimeStamp += '_'
	set @strBackupTimeStamp += convert(varchar, datepart(year, @dtNow)) 
	-- month
	set @strBackupTimeStamp += '_'
	set @lDatePart = datepart(month , @dtNow)
	if (@lDatePart < 10) set @strBackupTimeStamp += '0'
	set @strBackupTimeStamp += convert(varchar, @lDatePart)
	-- date
	set @strBackupTimeStamp += '_'
	set @lDatePart = datepart(dd, @dtNow)
	if (@lDatePart < 10) set @strBackupTimeStamp += '0'
	set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
	-- time
	set @strBackupTimeStamp += '_'
	-- hour
	set @lDatePart = datepart(hour, @dtNow)
	if (@lDatePart < 10) set @strBackupTimeStamp += '0'
	set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
	-- minute
	set @lDatePart = datepart(minute, @dtNow)
	if (@lDatePart < 10) set @strBackupTimeStamp += '0'
	set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
	-- seconds
	set @lDatePart = datepart(second, @dtNow)
	if (@lDatePart < 10) set @strBackupTimeStamp += '0'
	set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
	-- nanoseconds
	set @strBackupTimeStamp += '_'
	set @strBackupTimeStamp += left(datename(ns, SYSDATETIME()), 7)
	
	-- declare a variable for the backup name.
	declare @strBackupName nvarchar(500)
	-- build the backup name
	set @strBackupName = @dbName
	set @strBackupName += @strBackupTimeStamp	
	
	-- build the full database backup path.
	set @strDatabaseBackupPath += '\' + @strBackupName + '.bak'
	
	--print @strBackupName
	--print @strDatabaseBackupPath
	--print ''

	-- do the backup on the database
	--BACKUP DATABASE @dbName 
	--	TO DISK = N'E:\Backup\ReportServer\ReportServer_backup_2010_11_08_133443_7479175.bak' 
	--	WITH NOFORMAT, NOINIT,  
	--	NAME = N'ReportServer_backup_2010_11_08_133443_7323186', 
	--	SKIP, REWIND, NOUNLOAD,  STATS = 10
	BACKUP DATABASE @dbName 
		TO DISK = @strDatabaseBackupPath 
		WITH NOFORMAT, NOINIT,  
		NAME = @strBackupName, 
		SKIP, REWIND, NOUNLOAD,  STATS = 10

	-- get the next record
	fetch next from curDatabases into @dbName
end

-- close & deallocate the cursor
CLOSE curDatabases
DEALLOCATE curDatabases