Jak opravit poškozenou SQL databázi

Narazil jsem na problém, kdy po nekorektním vypnutí SQL serveru došlo k porušení databáze. Kromě toho byl poškozen transakční log (*.ldf) a databáze vypadala naprosto nepoužitelně. Příznaky vypadají následovně:

select * from [Databaze].Tabulka order by CreateDate
Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (1:105756) in database 21 failed. It belongs to allocation unit 72057594900840448 not to 72057594899529728.

Completion time: 2025-07-06T12:14:08.6294025+02:00

Pokusy odpojit databázi (Dettach) končí chybou.

Obvykle pomůže odpojit db do režimu Offline (Take Offline). Pak už odpojení (Dettach) projde.

Nyní přejmenujte soubor s transakčními logy (*.ldf), aby ho SQL server „neviděl“.

Nyní je třeba připojit databázi a SQL serveru říci, že má opravit (tedy znovuvytvořit) transakční log.

CREATE DATABASE [Databaze] 
ON (FILENAME = 'E:\MSSQL\DATA\Restore\Databaze.mdf') 
FOR ATTACH_REBUILD_LOG

Následně můžete opravit porušenou db.

alter database Databaze set emergency
go
alter database Databaze set single_user
go

use master
go
DBCC CHECKDB ('Databaze',  REPAIR_ALLOW_DATA_LOSS)

Výstup může vypadat nějak takto:

DBCC results for 'Databaze'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Repair: The extent (1:106056) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown).
Repair: The extent (1:105840) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901495808 (type Unknown).
Repair: The extent (1:106136) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901495808 (type Unknown).
Repair: The extent (1:106984) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901495808 (type Unknown).
Repair: The extent (1:107928) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901495808 (type Unknown).
Repair: The extent (1:109400) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901495808 (type Unknown).
Repair: The extent (1:106400) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901561344 (type Unknown).
Repair: The extent (1:107168) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901561344 (type Unknown).
Repair: The extent (1:108016) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901561344 (type Unknown).
Repair: The extent (1:109080) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901561344 (type Unknown).
Repair: The extent (1:109344) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594901561344 (type Unknown).
Msg 8905, Level 16, State 1, Line 34
Extent (1:105840) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
        The error has been repaired.
Msg 8904, Level 16, State 1, Line 34
Extent (1:105880) in database ID 9 is allocated by more than one allocation object.
        The error has been repaired.

....
....
....
....
CHECKDB found 14 allocation errors and 22 consistency errors in database 'Databaze'.
CHECKDB fixed 14 allocation errors and 0 consistency errors in database 'Databaze'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Databaze, repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Completion time: 2025-07-06T08:01:23.0437810-07:00

Nyní můžete vrátit databázi do provozu.

alter database PRDSaraRestore set online
go
alter database PRDSaraRestore set multi_user
go

Podrobnější popis použití DBCC najdete v tomto článku.