SQL:Start SQL Server Instance in single user mode and SUSPECT_PAGES table

CD \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

sqlservr.exe -m :Default Instance

sqlservr.exe -m -s :Named Instance

When will you start the SQL server instance to the single user mode?

• change server configuration options
• recover a damaged master database or other system database

If you cannot connect to the single mode, it is possible that the SQL Server agent service is running, which uses the single connection.

REPAIR_ALLOW_DATA_LOSS

Database must be in single user mode

ALTER DATABASE AdventureWorks SET SINGLE_USER;
GO
DBCC CHECKDB('database',REPAIR_ALLOW_DATA_LOSS);
GO
--After fixing the database, set it to multi-user mode
ALTER DATABASE AdventureWorks SET MULTI_USER;
GO


When you run DBCC CHECKDB('database',REPAIR_ALLOW_DATA_LOSS) in single user model, it updates the msdb.dbo.SUSPECT_PAGES table.

Procedures of restore a damaged data page:
1.Get the page id from msdb..suspect_pages;
2. Start a page restore with a full database, file, or filegroup backup that contains the page;

RESTORE DATABASE database_name
PAGE = 'file:page [ ,...n ]' [ ,...n ]
FROM [ ,...n ]
WITH NORECOVERY
3.

Apply the most recent differentials .
Apply the subsequent log backups.

4. Create a new log backup of the database that includes the final log serial number of the restored pages.
5. Restore the new log backup with RECOVERY

Example:
RESTORE DATABASE PAGE='1:57, 1:202, 1:916, 1:1016' FROM WITH NORECOVERY;
RESTORE LOG FROM WITH NORECOVERY;
RESTORE LOG FROM WITH NORECOVERY;
BACKUP LOG TO myNewlog
RESTORE LOG <database> FROM myNewlog WITH RECOVERY;
GO

Backup medium not good

RESTORE WITH CONTINUE_AFTER_ERROR

when CONTINUE_AFTER_ERROR is used in RESTORE Statement, Data pages that fail verification will be logged into msdb..SUSPECT_PAGES table.