-- 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