-- visada rasau AGP kas reiskia AG - inicialai, P - procedura, atitinkamai pvz funkcijos AGF
-- toliau is iprocio viskas angliskai :)

CREATE PROCEDURE AGP_DbMaitenance (
	@database nvarchar(50) = 'TestDB',
	@path nvarchar(2048) = 'c:\temp\',		-- for path will require permissions
	
	-- default: timestamps on
	@addTimestamp bit = 1,
	
	-- flags [by default on]
	@shrink bit = 1,
	@backup bit = 1
) AS

-----------------------------------------------------------------------------
--- (C) Arvydas Grigonis (aka Jornada Del Muerto on news), www.lythum.lt
-----------------------------------------------------------------------------
--- Here only sample writen for omni news 
-----------------------------------------------------------------------------
/*
-----------------------------------------------------------------------------
-- debug only code
-- this code part usage is if to run everything as script not procedure

declare @database nvarchar(50)
declare @path nvarchar(2048)
declare @addTimestamp bit
-- flags
declare @shrink bit
declare @backup bit

SET @path = 'C:\temp\'
SET @addTimestamp = 1
-- flagz
SET @shrink = 1
SET @backup = 1

-- Setting current DB as DB for backup
SELECT @database = DB_NAME()

-- end of debug code
-----------------------------------------------------------------------------
*/

-----------------------------------------------------------------------------
-- local variables
DECLARE @fullpath nvarchar(4000)	-- later full db backup path
DECLARE @appendix nvarchar(4000)	-- appendix is timestamp part

SET @appendix = '' -- initializing default val

PRINT '*** '+CONVERT(nvarchar, getDate(), 25)+' Starting maitenance for '+ @database +' db...'

-----------------------------------------------------------------------------
-- DB Backup http://msdn.microsoft.com/en-us/library/ms186865.aspx

IF @backup = 1 BEGIN

	IF @addTimestamp = 1 BEGIN
		-- adding timestamp
		-- nvarchar(10) = yyyy-MM-dd
		-- nvarchar(16) = yyyy-MM-dd hh:mm
		SET @appendix = '_' + CONVERT(nvarchar(10), getDate(), 25)
		-- don't needed for nvarchar(10)
		SET @appendix = REPLACE(@appendix, ':', '-')	-- filename not allow :
		SET @appendix = REPLACE(@appendix, ' ', '-')	-- more nice file name without spaces
	END
	
	-- crafting full backup file path
	SET @fullpath = @path + @database + @appendix + '.bak'
	
	PRINT '*** '+CONVERT(nvarchar, getDate(), 25)+' Backup '''+ @database +''' database to '''+ @fullpath +'''...'
	
	BACKUP DATABASE @database TO DISK = @fullpath -- WITH FORMAT;
	
END

-----------------------------------------------------------------------------
-- DB Shrink http://msdn.microsoft.com/en-us/library/ms190488.aspx

IF @shrink = 1 BEGIN
	PRINT '*** '+CONVERT(nvarchar, getDate(), 25)+' Srhinking database...'
	DBCC SHRINKDATABASE (@database, NOTRUNCATE) WITH NO_INFOMSGS;
END



-----------------------------------------------------------------------------
-- END
PRINT '*** '+CONVERT(nvarchar, getDate(), 25)+' Mainenance done!'
