
DBCC TRACEON (3604);
DBCC PAGE (9, 1, 401776, 0);
DBCC TRACEOFF (3604);

Select db_name(9)
GO
use <dbname_from_previous_query>
GO
Select object_name(946818435)
DBCC TRACEON (3604);
DBCC PAGE (9, 1, 401776, 0);
DBCC TRACEOFF (3604);
Select db_name(9)
GO
use <dbname_from_previous_query>
GO
Select object_name(946818435)
use master
restore database << dbname >> with recovery
Msg 4333, Level 16, State 1, Line 3 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.If you result with the above error message, then we should understand that the particular database is not restored in a proper way. May be. the restore process would have terminated due to network issues or cancelled the restore part way through etc. Such database cannot be brought online. The best way would be to drop the database and restore from the backup.
restore database << dbname >> from disk ='<filepath>\<backup filename>'
ALTER DATABASE dbname SET EMERGENCY
User does not have permission to alter database ‘dbname’, the database does not exist, or the database is not in a state that allows access checks.How to drop SUSPECT database? As we identified that this database has severely corrupted and it cannot be repair nor recovered, there is no way other than dropping the database. If you have backup of this database, you can always do restore from the latest valid backup (even up to the point in time). But unfortunately, in our case, it will not even allow you to drop and it would end up with the above same error message for DROP as well. So, how do we drop this db? Its bit tricky and needs some outage of SQL Server (since one of the step is stopping the service).
DECLARE @path NVARCHAR(4000)
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
@path OUTPUT,
'no_output'
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
REG_SZ,
N'\\xx.xxx.xx.xxx\C$\data\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup';
DECLARE @SQLDataRoot nvarchar(512)
DECLARE @DefaultData nvarchar(512)
DECLARE @DefaultLog nvarchar(512)
--Installation Root Info
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\Setup',
N'SQLDataRoot',
@SQLDataRoot OUTPUT
Select @SQLDataRoot
-- SQL Data file Info
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@DefaultData OUTPUT
Select @DefaultData
-- SQL Default Default Log file info
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@DefaultLog OUTPUT
Select @DefaultLog
xp_instance_regread & xp_instance_regwrite are undocumented features, so it may be deprecated any time, however, can be used for non-production or DBA specific tasks at own discretion.I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Script
DECLARE @DBNAME nvarchar(100)
,@FileName nvarchar(max)
,@Status nvarchar(10)
SET @DBNAME = NULL -- Provide [DBNAME]
SET @Status = 'OFFLINE' --Provide [OFFLINE / ONLINE]
SELECT @FileName=[path] FROM sys.traces WHERE is_default=1
DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max))
INSERT INTO @ErrorLogTable
EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc'
SELECT Distinct DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime, B.ProcessInfo, B.Text
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) A
Inner join @ErrorLogTable B on A.SPID = cast(SUBSTRING(B.ProcessInfo,5,5) AS int)
and CAST(StartTime AS nvarchar)=cast(B.Logdate AS nvarchar)