-- Name: sp_add_file_recover_suspect_db
-- Purpose: Adds a data or log file to a suspect database and runs
-- 		recovery on the database.  This SP should only be used
--		on databases that have been marked suspect due to
--		insufficient data (error 1105) or log (error 9002) space.
-- Note: This SP is not documented.  Only sp_add_data_file_recover_suspect_db
--	and sp_add_log_file_recover_suspect_db below are documented

create procedure sys.sp_add_file_recover_suspect_db
	@dbName 	sysname					-- database name
	,@filetype	nvarchar(4)				-- "data" or "log"
	,@filegroup	nvarchar(260)			-- file group for new file
	,@name		nvarchar(260)			-- logical file name
	,@filename	nvarchar(260)			-- OS file name
	,@size		nvarchar(20) 	= NULL	-- initial file size
	,@maxsize	nvarchar(20) 	= NULL	-- maximum file size
	,@filegrowth nvarchar(20) 	= NULL	-- growth increment

as
	declare @addcmd nvarchar(max)
	declare @islog	int

	set nocount on

	-- RESTRICT TO SA 
	if (not (is_srvrolemember('sysadmin') = 1))
	begin
	   raiserror(15247,-1,-1)
	   return (1)
	end

	-- DETERMINE IF THIS DATA/LOG FILE 
	if (upper(@filetype) = 'DATA')
		select @islog = 0
	else if (upper(@filetype) = 'LOG')
		select @islog = 1
	else
	begin
		print 'Must specify data or log file type'
		return (1)
	end

	set implicit_transactions off
	if @@trancount > 0
	begin
	   raiserror(15002,-1,-1,'sys.sp_add_file_recover_suspect_db')
	   return (1)
	end

	-- Check if db exists
	if not exists (select * from master.dbo.sysdatabases where name = @dbName)
	begin
		raiserror(15010,-1,-1,@dbName)
		return (1)
	end

	-- Build the Alter Database Set Emergency string
	select @addcmd = 'ALTER DATABASE ' + quotename(@dbName, N'[') + ' SET EMERGENCY'
	EXEC(@addcmd)
	
	-- Build the Alter Database Add File string
	select @addcmd = 'ALTER DATABASE ' + quotename(@dbName, N'[') + ' ADD'
	if (@islog = 1)
		select @addcmd = @addcmd + ' LOG FILE'
	else
		select @addcmd = @addcmd + ' FILE'

	select @addcmd = @addcmd + '(NAME = ' + quotename(@name, N'[') + ', FILENAME = ''' + REPLACE(@filename,'''','''''') + ''''
	if (@size is not null)
		select @addcmd = @addcmd + ', SIZE = ' + @size
	if (@maxsize is not null)
		select @addcmd = @addcmd + ', MAXSIZE = ' + @maxsize
	if (@filegrowth is not null)
		select @addcmd = @addcmd + ', FILEGROWTH = ' + @filegrowth
	select @addcmd = @addcmd + ' )'
	if (@filegroup is not null)
		select @addcmd = @addcmd + ' TO FILEGROUP ' + quotename(@filegroup, N'[') + ''
	print @addcmd

	EXEC(@addcmd)

	-- Clear emergency-mode
	-- Build the Alter Database Set Online string
	-- note that this will clear Emergency and Suspect and run recovery
	select @addcmd = 'ALTER DATABASE ' + quotename(@dbName, N'[') + ' SET ONLINE'
	EXEC(@addcmd)

	-- Success 
	return (0)	-- sp_add_file_recover_suspect_db