-- Drop old database

USE [master];

GO

IF DATABASEPROPERTYEX (N'Company', N'Version') > 0

BEGIN

ALTER DATABASE [Company] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [Company];

END

-- Create database and table

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

USE [Company];

GO

CREATE TABLE [Sales] (

[SalesID] INT IDENTITY,

[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),

[SalesDate] DATETIME DEFAULT GETDATE (),

[SalesAmount] MONEY DEFAULT CONVERT (MONEY, 100 * RAND ()));

CREATE CLUSTERED INDEX [SalesCI] ON [Sales] ([SalesID]);

GO

-- Populate the table

SET NOCOUNT ON;

GO

INSERT INTO [Sales] DEFAULT VALUES;

GO 5000

-- Create some nonclustered indexes

CREATE NONCLUSTERED INDEX [SalesNCI_CustomerID] ON [Sales] ([CustomerID]);

CREATE NONCLUSTERED INDEX [SalesNCI_SalesDate_SalesAmount] ON [Sales] ([SalesDate]) INCLUDE ([SalesAmount]);

GO

-- Create a good backup

BACKUP DATABASE [Company] TO DISK = N'C:\SQLskills\OldCompany.bck'

WITH INIT;

-- And detach it

USE [master]

GO

EXEC sp_detach_db N'Company';

GO

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

Msg 1813, Level 16, State 2, Line 5

Could not open new database 'Company'. CREATE DATABASE is aborted.

Msg 824, Level 24, State 2, Line 5

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

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

ALTER DATABASE [Company] SET ONLINE;

GO

Msg 5181, Level 16, State 5, Line 33

Could not restart database "Company". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 33

ALTER DATABASE statement failed.

Msg 824, Level 24, State 2, Line 33

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

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

GO

ALTER DATABASE [Company] SET EMERGENCY;

GO

ALTER DATABASE [Company] SET SINGLE_USER;

GO

Msg 824, Level 24, State 2, Line 43

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

ALTER DATABASE [Company] SET OFFLINE;

GO

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

DROP DATABASE [Company];

GO

RESTORE DATABASE [Company] FROM

DISK = N'C:\SQLskills\OldCompany.bck'

WITH REPLACE;

GO

ALTER DATABASE [Company] SET OFFLINE;

GO

ALTER DATABASE [Company] SET ONLINE;

GO

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