/****** Object:  Stored Procedure dbo.sp_recoverydisk    Script Date: 12/06/99 08:09:26 ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_recoverydisk') and sysstat & 0xf = 4)
	drop procedure dbo.sp_recoverydisk
GO

/****** Object:  Stored Procedure dbo.sp_recoverytask    Script Date: 12/06/99 08:09:26 ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_recoverytask') and sysstat & 0xf = 4)
	drop procedure dbo.sp_recoverytask
GO

/****** Object:  Stored Procedure dbo.sp_structurescript    Script Date: 12/06/99 08:09:26 ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_structurescript') and sysstat & 0xf = 4)
	drop procedure dbo.sp_structurescript
GO

/****** Object:  Stored Procedure dbo.sp_recoverydisk    Script Date: 12/06/99 08:09:26 ******/
CREATE PROCEDURE sp_recoverydisk
	
/*
** Title:       Create Recovery Disk
** Version:	1.0
** Date:        12 VI 1999
** Author:      Jarlath O'Grady MCP
** Email:       jarlath.ogrady@dnet.co.uk
** SQL Version: 6.5
** Description: Executes stored procedures sp_structurescript and sp_help_revdatabase through ISQL to
**		produce files on floppy disk.
** Comments:	The SA login is used for execution. To hide the SA password, it is stored within the command
**		of the task. This is only visible by SA or DBO of msdb.
*/
	@pass	VARCHAR(30)
AS

DECLARE	@name	VARCHAR(255),
	@cmd	VARCHAR(255),
	@qry	VARCHAR(255),
	@total	SMALLINT,
	@return	SMALLINT

SET	NOCOUNT ON

SELECT	@total = 0,
	@return = 0



-- Generate Recovery details
SELECT	@qry = 'isql -Usa -P'+@pass+' -dmaster -n -w960 /Qmaster..sp_structurescript  /oa:\recovery.out'
EXECUTE	xp_cmdshell @qry

-- Create Script for All Databases
SELECT	@qry = 'isql /Usa -P'+@pass+' /dmaster /S' +@@SERVERNAME+ ' /Qsp_help_revdatabase /oa:\alldbs.sql'
EXECUTE	xp_cmdshell @qry

DECLARE dblist CURSOR
FOR
	SELECT	name
	FROM	sysdatabases

OPEN	dblist


FETCH	NEXT FROM dblist INTO @name


WHILE	@@FETCH_STATUS <> -1
	BEGIN

-- Create Script for Each Database
	SELECT	@cmd = 'sp_help_revdatabase ' +@name

	SELECT	@qry = 'isql /Usa -P'+@pass+' /dmaster /S' +@@SERVERNAME+ ' /Q"' +@cmd+ '" /oa:\' +@name+ '.sql'

	EXECUTE	@return = xp_cmdshell @qry

	SELECT	@total = @total + @return

	FETCH	NEXT FROM dblist INTO @name
	END


CLOSE	dblist

DEALLOCATE dblist

IF	@total != 0
	RAISERROR ('Database scripts not created successfully!', 1, 1)

SET	NOCOUNT OFF
GO

/****** Object:  Stored Procedure dbo.sp_recoverytask    Script Date: 12/06/99 08:09:26 ******/
CREATE PROCEDURE sp_recoverytask

/*
** Title:       Create Recovery Task
** Version:	1.0
** Date:        12 VI 1999
** Author:      Jarlath O'Grady MCP
** Email:       jarlath.ogrady@dnet.co.uk
** SQL Version: 6.5
** Description: Creates task, sheduled to run daily at 12:00 am, that executes sp_structurescript, which
**		saves SQL Server configuration settings to floppy disk.
** Comments:	The SA login is used for execution. To hide the SA password, it is stored within the command
**		of the task. This is only visible by SA or DBO of msdb.
*/

	@TASK	VARCHAR(100),
	@PASS	VARCHAR(30),
	@STATUS	TINYINT = 0
AS

DECLARE	@PROC	TINYINT,
	@COMM	VARCHAR(255),
	@PRINT	VARCHAR(255)

SET	NOCOUNT ON

--Check for existance of task 
IF	EXISTS (SELECT 1 FROM msdb..systasks WHERE name = @TASK)
	BEGIN
	IF	@STATUS = 1 
		BEGIN
		EXECUTE msdb..sp_droptask 'Recovery Disk'
		SELECT	@PRINT = "'"+@TASK+"' task already exists and has been removed."
		PRINT	@PRINT 
		PRINT	''
		END
	ELSE	
		BEGIN
		SELECT	@PRINT = "'"+@TASK+"' task already exists. Please enter a different task name."

		PRINT	@PRINT 
		PRINT	''
		RETURN
		END
	END


PRINT	'Checking floppy disk in A drive.'
PRINT	''

--Check diskette for format 
EXECUTE	@PROC = master..xp_cmdshell 'dir a:', NO_OUTPUT

--Check the execution of task 
IF	@PROC != 0
	BEGIN
	SELECT	@PRINT = 'Check there is a formatted floppy disk in A drive!'
	PRINT	@PRINT
	PRINT	''
	END
ELSE	
	BEGIN
	SELECT	@PRINT = 'Floppy disk in A drive is ready'
	PRINT	@PRINT
	PRINT	''
	END

SELECT	@COMM = 'EXECUTE sp_recoverydisk "' +@PASS+ '"'

--Execute task 
EXECUTE @PROC = msdb..sp_addtask	@name = @TASK, @enabled = 1, @subsystem = 'TSQL', @freqtype = 4,
					@command = @COMM 


--Check the execution of task 
IF	@PROC != 0
	BEGIN
	SELECT	@PRINT = "Creation of task '"+@TASK+"' caused error."
	PRINT	@PRINT
	PRINT	''
	END
ELSE	
	BEGIN
	SELECT	@PRINT = "Creation of task '"+@TASK+"' successful."
	PRINT	@PRINT
	PRINT	''
	END

SET	NOCOUNT OFF
GO

/****** Object:  Stored Procedure dbo.sp_structurescript    Script Date: 12/06/99 08:09:26 ******/
CREATE PROCEDURE sp_structurescript AS 

/*
** Title:       SQL Server Settings for Recovery 
** Version:	1.1
** Date:        12 VI 1999
** Author:      Jarlath O'Grady MCP
** Email:       jarlath.ogrady@dnet.co.uk
** SQL Version: 6.5
** Description: Display current time, 
**                      server name, 
**                      user, 
**                      SQL Server version,
**                      sort order,
**                      character set,
**                      standard & advanced configuration options,
**                      database structure,
**                      device structure
** Comments: 	This stored procedure is designed to be re-scheduled daily by task 'Structure 
**              History Refresh' to run once and to generate ouput file to disk,   but it can 
**              be run manually at any time
*/

DECLARE	@DATE	DATETIME,
	@PRINT1	VARCHAR(255),
	@PRINT2	VARCHAR(255),
	@PRINT3	VARCHAR(255),
	@WIDTH	TINYINT

SET	NOCOUNT ON
SELECT	@DATE = GETDATE() 

EXECUTE	sp_configure 'show advanced options', 1
RECONFIGURE

SELECT	@PRINT1 = 'Report of Recovery Information for Server '+@@SERVERNAME
SELECT	@PRINT2 = 'Generated on '+CONVERT(CHAR(20), @DATE, 113)+' by '+USER_NAME()

IF	DATALENGTH (@PRINT1) >= DATALENGTH (@PRINT2) 
	SELECT	@WIDTH = DATALENGTH (@PRINT1) 
ELSE	
	SELECT	@WIDTH = DATALENGTH (@PRINT2) 

PRINT	''
SELECT	@PRINT3 = '/' + REPLICATE ('*', @WIDTH - 1)
PRINT	@PRINT3
PRINT	''
PRINT	@PRINT1
PRINT	''
PRINT	@PRINT2
PRINT	''
SELECT	@PRINT3 = REPLICATE ('*', @WIDTH - 1) + '/'
PRINT	@PRINT3
PRINT	''



PRINT	''
SELECT	@@VERSION AS 'Current SQL Server Version'

PRINT	''
PRINT	'Current Sort Order & Character Set'
PRINT	'-------=----------=-=---------=---'
PRINT	''
EXECUTE	sp_helpsort

PRINT	''
PRINT	'Current Standard & Advanced Configuration Options'

PRINT	'-------=--------=-=--------=-------------=-------'
PRINT	''
EXECUTE	sp_configure

PRINT	''
PRINT	'Current Database Structure'
PRINT	'-------=--------=---------'
PRINT	''
EXECUTE	sp_helpdb
PRINT	''

PRINT	''
PRINT	'Current Device Structure'
PRINT	'-------=------=---------'
PRINT	''
EXECUTE	sp_helpdevice
PRINT	''

EXECUTE	sp_configure 'show advanced options', 0
RECONFIGURE

SET	NOCOUNT OFF

GO