/********************************************************************
	Automated backup creation script.  This will create a workspace 
	database on the server, a system schema and two tables for tracking
	backups.

	Use the Query -> Specify Values for Template Parameters... menu option
	to set the options for your system.

	Copywright (c) Aaron Ingold - 2006
	This is yours to use and modify as you see fit, so long as
	you give credit to the original author and do not republish without
	permission.
********************************************************************/
PRINT ('Creating database, schema and tables.')
CREATE DATABASE <DBA_Workspace_Database,sysname,__dba_workspace>

GO

USE <DBA_Workspace_Database,sysname,__dba_workspace>

GO
CREATE SCHEMA backups
	CREATE TABLE	backups.db_full_backups (
		database_nm	SYSNAME	NOT NULL
	,	backup_full_file_nm	VARCHAR ( MAX )	NOT NULL
	,	backup_create_dt	DATETIME	NOT NULL	DEFAULT GETDATE()
	,	backup_expiration_dt	DATETIME	NOT NULL
	,	backup_removed_dt	DATETIME
	)

	CREATE TABLE	backups.db_log_backups (
		database_nm	SYSNAME	NOT NULL
	,	backup_full_file_nm	VARCHAR ( MAX )	NOT NULL
	,	backup_create_dt	DATETIME	NOT NULL	DEFAULT GETDATE()
	,	backup_expiration_dt	DATETIME	NOT NULL
	,	backup_removed_dt	DATETIME
	)

	CREATE TABLE	backups.ignore_databases (
		database_nm	SYSNAME	NOT NULL
	)
GO

INSERT	backups.ignore_databases
VALUES ('AdventureWorks')

INSERT	backups.ignore_databases
VALUES ('AdventureWorksDW')

GO

PRINT ('<DBA_Workspace_Database,sysname,__dba_workspace> created.  Tables added.')
PRINT ('Adding subdirectories for system databases.')

EXECUTE master.dbo.xp_create_subdir N'<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\master'
GO
EXECUTE master.dbo.xp_create_subdir N'<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\model'
GO
EXECUTE master.dbo.xp_create_subdir N'<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\msdb'
GO

PRINT ('Starting System DB Backup creation.')

EXEC msdb.dbo.sp_add_job
	@job_name = 'System DB Full Backup'
,	@enabled = 1
,	@category_name = 'Database Maintenance'
,	@notify_level_eventlog = 3			-- Always write to event log

EXEC msdb.dbo.sp_add_jobserver
	@job_name = 'System DB Full Backup'

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'System DB Full Backup'
,	@step_name = N'Backup master database.'
,	@subsystem = N'TSQL'
,	@command = 
		N'DECLARE @file	NVARCHAR ( 1000 )
		SET @file = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\master\master_backup_'' + CONVERT(NVARCHAR, GETDATE(), 112) + ''.bak''
		BACKUP DATABASE master 
			TO DISK = @file
			WITH	RETAINDAYS = <system_bak_retaindays,int,3>

		INSERT <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
		VALUES (''master'', @file, GETDATE(), DATEADD(dd, <system_bak_retaindays,int,3>, GETDATE()), NULL)'
,	@on_success_action = 3
,	@on_fail_action = 2

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'System DB Full Backup'
,	@step_name = N'Backup model database.'
,	@subsystem = N'TSQL'
,	@command = 
		N'DECLARE @file	NVARCHAR ( 1000 )
		SET @file = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\model\model_backup_'' + CONVERT(NVARCHAR, GETDATE(), 112) + ''.bak''
		BACKUP DATABASE model 
			TO DISK = @file
			WITH	RETAINDAYS = <system_bak_retaindays,int,3>

		INSERT <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
		VALUES (''model'', @file, GETDATE(), DATEADD(dd, <system_bak_retaindays,int,3>, GETDATE()), NULL)'
,	@on_success_action = 3
,	@on_fail_action = 2

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'System DB Full Backup'
,	@step_name = N'Backup msdb database.'
,	@subsystem = N'TSQL'
,	@command = 
		N'DECLARE @file	NVARCHAR ( 1000 )
		SET @file = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\msdb\msdb_backup_'' + CONVERT(NVARCHAR, GETDATE(), 112) + ''.bak''
		BACKUP DATABASE msdb 
			TO DISK = @file
			WITH	RETAINDAYS = <system_bak_retaindays,int,3>

		INSERT <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
		VALUES (''msdb'', @file, GETDATE(), DATEADD(dd, <system_bak_retaindays,int,3>, GETDATE()), NULL)'
,	@on_success_action = 3
,	@on_fail_action = 2

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'System DB Full Backup'
,	@step_name = N'Clean up old system database backups.'
,	@subsystem = N'TSQL'
,	@command = 
		N'BEGIN TRAN

		BEGIN TRY

		DECLARE @backup_file	NVARCHAR ( 1000 )

		DECLARE del_cur CURSOR FOR
		SELECT backup_full_file_nm
		FROM <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
		WHERE database_nm IN (''master'', ''model'', ''msdb'')
		AND	backup_expiration_dt <= GETDATE()
		AND backup_removed_dt IS NULL

		OPEN del_cur
		FETCH NEXT FROM del_cur INTO @backup_file

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC master.sys.xp_delete_file 0, @backup_file

			UPDATE	<DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
			SET backup_removed_dt = GETDATE()
			WHERE	backup_full_file_nm = @backup_file

			FETCH NEXT FROM del_cur INTO @backup_file
		END

		CLOSE del_cur
		DEALLOCATE del_cur

		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRAN
			DECLARE @err_msg	NVARCHAR (2000)
			,	@err_sev	INT
			,	@err_state	INT

			SELECT @err_msg = ERROR_MESSAGE()
			,	@err_sev = ERROR_SEVERITY()
			,	@err_state = ERROR_STATE()

			RAISERROR (@err_msg, @err_sev, @err_state)
		END CATCH

		IF @@TRANCOUNT > 0 COMMIT TRAN
		'
,	@on_success_action = 1
,	@on_fail_action = 2

PRINT ('Created system backup jobs.')

-- Next the user db jobs.

PRINT ('Creating user database backup jobs.')

EXEC msdb.dbo.sp_add_job
	@job_name = 'User DB Full Backup'
,	@enabled = 1
,	@category_name = 'Database Maintenance'
,	@notify_level_eventlog = 3			-- Always write to event log

EXEC msdb.dbo.sp_add_jobserver
	@job_name = 'User DB Full Backup'

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'User DB Full Backup'
,	@step_name = N'Backup user databases.'
,	@subsystem = N'TSQL'
,	@command = N'
		DECLARE @folder_nm	NVARCHAR ( 500 )
		,	@rc	INT
		,	@name	SYSNAME
		,	@file	NVARCHAR ( 1000 )

		DECLARE @dirtree	TABLE (subdirectory	NVARCHAR ( 255 ), depth	INT)

		SET @folder_nm = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>''
			
		INSERT @dirtree
		EXEC master.sys.xp_dirtree @folder_nm

		DECLARE backup_cur	CURSOR FOR
		SELECT	name 
		FROM	master.sys.databases
		WHERE	name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
		AND	name NOT IN (SELECT database_nm FROM <DBA_Workspace_Database,sysname,__dba_workspace>.backups.ignore_databases)

		OPEN backup_cur
		FETCH NEXT FROM backup_cur INTO @name

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @folder_nm = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\'' + @name
				
			IF NOT EXISTS (SELECT 1 FROM @dirtree WHERE subdirectory = @name)
				EXECUTE master.dbo.xp_create_subdir @folder_nm

			SET @file = @folder_nm + ''\'' + @name + ''_backup_'' + CONVERT(NVARCHAR, GETDATE(), 112) + ''.bak''
			
			BACKUP DATABASE @name
				TO DISK = @file
				WITH	RETAINDAYS = <user_bak_retaindays,int,5>

			INSERT <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
			VALUES (@name, @file, GETDATE(), DATEADD(dd, <user_bak_retaindays,int,5>, GETDATE()), NULL)

			FETCH NEXT FROM backup_cur INTO @name
		END

		CLOSE backup_cur
		DEALLOCATE backup_cur
		'		
,	@on_success_action = 3
,	@on_fail_action = 2

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'User DB Full Backup'
,	@step_name = N'Clean up old user database backups.'
,	@subsystem = N'TSQL'
,	@command = 
		N'BEGIN TRAN

		BEGIN TRY

		DECLARE @backup_file	NVARCHAR ( 1000 )

		DECLARE del_cur CURSOR FOR
		SELECT backup_full_file_nm
		FROM <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
		WHERE database_nm NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
		AND	backup_expiration_dt <= GETDATE()
		AND backup_removed_dt IS NULL

		OPEN del_cur
		FETCH NEXT FROM del_cur INTO @backup_file

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC master.sys.xp_delete_file 0, @backup_file

			UPDATE	<DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_full_backups
			SET backup_removed_dt = GETDATE()
			WHERE	backup_full_file_nm = @backup_file

			FETCH NEXT FROM del_cur INTO @backup_file
		END

		CLOSE del_cur
		DEALLOCATE del_cur

		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRAN
			DECLARE @err_msg	NVARCHAR (2000)
			,	@err_sev	INT
			,	@err_state	INT

			SELECT @err_msg = ERROR_MESSAGE()
			,	@err_sev = ERROR_SEVERITY()
			,	@err_state = ERROR_STATE()

			RAISERROR (@err_msg, @err_sev, @err_state)
		END CATCH

		IF @@TRANCOUNT > 0 COMMIT TRAN
		'
,	@on_success_action = 1
,	@on_fail_action = 2

PRINT ('Created user database backup jobs.')

-- Finally create the user DB T-Log backup job

PRINT ('Creating user database log backup jobs.')

EXEC msdb.dbo.sp_add_job
	@job_name = 'User DB Log Backup'
,	@enabled = 1
,	@category_name = 'Database Maintenance'
,	@notify_level_eventlog = 3			-- Always write to event log

EXEC msdb.dbo.sp_add_jobserver
	@job_name = 'User DB Log Backup'

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'User DB Log Backup'
,	@step_name = N'Backup user database logs.'
,	@subsystem = N'TSQL'
,	@command = N'
		DECLARE @folder_nm	NVARCHAR ( 500 )
		,	@rc	INT
		,	@name	SYSNAME
		,	@file	NVARCHAR ( 1000 )

		DECLARE @dirtree	TABLE (subdirectory	NVARCHAR ( 255 ), depth	INT)

		SET @folder_nm = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>''
			
		INSERT @dirtree
		EXEC master.sys.xp_dirtree @folder_nm

		DECLARE backup_cur	CURSOR FOR
		SELECT	name 
		FROM	master.sys.databases
		WHERE	name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
		AND	recovery_model_desc <> ''SIMPLE''
		AND	DATEDIFF(day, create_date, GETDATE()) >= 1		-- Must perform 1 full backup before you can do log backups.
		AND	name NOT IN (SELECT database_nm FROM <DBA_Workspace_Database,sysname,__dba_workspace>.backups.ignore_databases)

		OPEN backup_cur
		FETCH NEXT FROM backup_cur INTO @name

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @folder_nm = ''<Backup_Dir_Path,nvarchar(255),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>\'' + @name
				
			IF NOT EXISTS (SELECT 1 FROM @dirtree WHERE subdirectory = @name)
				EXECUTE master.dbo.xp_create_subdir @folder_nm

			SET @file = @folder_nm + ''\'' + @name + ''_tlog_'' + CONVERT(NVARCHAR, GETDATE(), 112) + LEFT(REPLACE(CONVERT(VARCHAR, GETDATE(), 108), '':'', ''''), 4)+ ''.trn''
			
			BACKUP LOG @name
				TO DISK = @file
				WITH	RETAINDAYS = <user_log_retaindays,int,1>

			INSERT <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_log_backups
			VALUES (@name, @file, GETDATE(), DATEADD(dd, <user_log_retaindays,int,1>, GETDATE()), NULL)

			FETCH NEXT FROM backup_cur INTO @name
		END

		CLOSE backup_cur
		DEALLOCATE backup_cur
		'		
,	@on_success_action = 3
,	@on_fail_action = 2

EXEC msdb.dbo.sp_add_jobstep
	@job_name = 'User DB Log Backup'
,	@step_name = N'Clean up old user database logs.'
,	@subsystem = N'TSQL'
,	@command = 
		N'BEGIN TRAN

		BEGIN TRY

		DECLARE @backup_file	NVARCHAR ( 1000 )

		DECLARE del_cur CURSOR FOR
		SELECT backup_full_file_nm
		FROM <DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_log_backups
		WHERE database_nm NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
		AND	backup_expiration_dt <= GETDATE()
		AND backup_removed_dt IS NULL

		OPEN del_cur
		FETCH NEXT FROM del_cur INTO @backup_file

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC master.sys.xp_delete_file 0, @backup_file

			UPDATE	<DBA_Workspace_Database,sysname,__dba_workspace>.backups.db_log_backups
			SET backup_removed_dt = GETDATE()
			WHERE	backup_full_file_nm = @backup_file

			FETCH NEXT FROM del_cur INTO @backup_file
		END

		CLOSE del_cur
		DEALLOCATE del_cur

		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0 ROLLBACK TRAN
			DECLARE @err_msg	NVARCHAR (2000)
			,	@err_sev	INT
			,	@err_state	INT

			SELECT @err_msg = ERROR_MESSAGE()
			,	@err_sev = ERROR_SEVERITY()
			,	@err_state = ERROR_STATE()

			RAISERROR (@err_msg, @err_sev, @err_state)
		END CATCH

		IF @@TRANCOUNT > 0 COMMIT TRAN
		'
,	@on_success_action = 1
,	@on_fail_action = 2

GO

PRINT ('Created user database log backup jobs.')

PRINT ('
Taking an initial backup of all databases.')

EXEC msdb.dbo.sp_start_job 
	@job_name = 'System DB Full Backup'
GO
EXEC msdb.dbo.sp_start_job
	@job_name = 'User DB Full Backup'
GO
PRINT ('
Execution completed, jobs created.
Next steps are to add an Execute SQL Server Agent Job Task to each maintenance plan 
to execute the full backup jobs.
Finally, configure a schedule and attach it to the log backup jobs.')