-- Hacking a broken boot page

USE [master];
GO

-- Try attaching it again
EXEC sp_attach_db @dbname = N'Company', 
    @filename1 = N'D:\SQLskills\Company.mdf', 
    @filename2 = N'D:\SQLskills\Company_log.ldf';
GO

-- Attached?
SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

-- Try hack attach method
-- Create dummy database, switch in old files
CREATE DATABASE [Company] ON PRIMARY (
    NAME = N'Company',
    FILENAME = N'D:\SQLskills\Company.mdf')
LOG ON (
    NAME = N'Company_log',
    FILENAME = N'D:\SQLskills\Company_log.ldf');
GO

ALTER DATABASE [Company] SET OFFLINE;
GO

-- ***** Copy in the corrupt files
-- Set the security

-- Now try using the database
ALTER DATABASE [Company] SET ONLINE;
GO

-- Can we get in to the database?
SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

USE [Company];
GO

ALTER DATABASE [Company] SET EMERGENCY;
GO
ALTER DATABASE [Company] SET SINGLE_USER;
GO

USE [Company];
GO

-- Try hacking the boot page

-- Set the database offline do we can copy the files
ALTER DATABASE [Company] SET OFFLINE;
GO

-- ***** Copy off the corrupt files

DROP DATABASE [Company];
GO

-- Restore an older copy
RESTORE DATABASE [Company] FROM
DISK = N'C:\SQLskills\OldCompany.bck'
WITH REPLACE;
GO

-- Set this new database offline to give up file locks
ALTER DATABASE [Company] SET OFFLINE;
GO

-- Boot page copy, from offset 73728
-- Be very careful to overwrite, not insert!!

-- Now delete the restored database files and
-- drop in the fixed one

-- Try to bring it online
ALTER DATABASE [Company] SET ONLINE;
GO

SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

-- Proceed with EMERGENCY-mode repair
ALTER DATABASE [Company] SET EMERGENCY;
GO
ALTER DATABASE [Company] SET SINGLE_USER;
GO
DBCC CHECKDB (N'Company', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

-- Now try again...
SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

USE [Company];
GO

SELECT * FROM [Sales];
GO

-- :-)

-- So it works, but emergency-mode repair won't be able
-- to salvage anything from the log file. But it works!

-- Does boot page hack work by copying from a different database?
-- Yes, enough to access the data sometimes, but NOT advisable in
-- production because it copies things like the database ID and other
-- boot page flags and settings.

-- Does it work for fileheader pages?
USE [master]
GO

EXEC sp_detach_db N'Company';
GO

-- Corrupt the file header page with HxD

USE [master];
GO

-- Try attaching it again
EXEC sp_attach_db @dbname = N'Company', 
    @filename1 = N'D:\SQLskills\Company.mdf', 
    @filename2 = N'D:\SQLskills\Company_log.ldf';
GO

-- Attached?
SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

-- Try hack attach method
-- Create dummy database, switch in old files
CREATE DATABASE [Company] ON PRIMARY (
    NAME = N'Company',
    FILENAME = N'D:\SQLskills\Company.mdf')
LOG ON (
    NAME = N'Company_log',
    FILENAME = N'D:\SQLskills\Company_log.ldf');
GO

ALTER DATABASE [Company] SET OFFLINE;
GO

-- ***** Copy in the corrupt files

-- Now try using the database
ALTER DATABASE [Company] SET ONLINE;
GO

-- Can we get in to the database?
SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

USE [Company];
GO

ALTER DATABASE [Company] SET EMERGENCY;
GO
ALTER DATABASE [Company] SET SINGLE_USER;
GO

USE [Company];
GO

-- Try hacking the fileheader page

-- Set the database offline do we can copy the files
ALTER DATABASE [Company] SET OFFLINE;
GO

-- ***** Copy off the corrupt files

DROP DATABASE [Company];
GO

-- Restore an older copy
RESTORE DATABASE [Company] FROM
DISK = N'C:\SQLskills\OldCompany.bck'
WITH REPLACE;
GO

-- Set this new database offline to give up file locks
ALTER DATABASE [Company] SET OFFLINE;
GO

-- Fileheader page copy, from offset 0
-- Be very careful to overwrite, not insert!!

-- Now delete the restored database files and
-- drop in the fixed one

-- Try to bring it online
ALTER DATABASE [Company] SET ONLINE;
GO

SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

-- Proceed with EMERGENCY-mode repair
ALTER DATABASE [Company] SET EMERGENCY;
GO
ALTER DATABASE [Company] SET SINGLE_USER;
GO
DBCC CHECKDB (N'Company', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

-- Now try again...
SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');
GO

USE [Company];
GO

ALTER DATABASE [Company] SET MULTI_USER;
GO

SELECT * FROM [Sales];
GO

