Mehmet AYDIN

Arasıra başımıza gelen bir olaydır. Elimizde bir şekilde sadece MDF (Data File) kalmıştır. LDF (Log File) silinmiştir veya elimizde mevcut değildir. Sadece MDF Dosyasından aşağıdaki yöntem ile Database’i SQL Server 2005 veya SQL Server 2008 e attach edebiliriz.

1. MDF Dosyamız ile aynı isimli bir veritabanı oluşturuyoruz.



2. SQL Serverı durdurup, yeni yarattığımız MDF dosyasını silip, yerine elimizde bulunan MDF dosyasını kopyalıyoruz.
3. Kopyalama işlemi tamamlandıktan sonra, SQL Servisini tekrar başlatıyoruz.
4. Aşağıdaki komutu çalıştırıyoruz

USE [master]
GO
ALTER DATABASE [DATABASEADI] SET EMERGENCY
GO
ALTER DATABASE [DATABASEADI] SET SINGLE_USER
GO
DBCC CHECKDB ([DATABASEADI], REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [DATABASEADI] SET MULTI_USER
GO
ALTER DATABASE [DATABASEADI] SET ONLINE
GO



5. Komutu çalıştırdıktan sonra aşağıdaki gibi bir hata alacaksınız

Msg 5173, Level 16, State 1, Line 1

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

Log file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase_log.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

Warning: The log for database ‘TestDatabase’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

DBCC results for ‘TestDatabase’.

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.

DBCC results for ‘sys.sysrowsetcolumns’.

There are 538 rows in 5 pages for object “sys.sysrowsetcolumns”.

DBCC results for ‘sys.sysrowsets’.

There are 78 rows in 1 pages for object “sys.sysrowsets”.

DBCC results for ‘sysallocunits’.

There are 89 rows in 1 pages for object “sysallocunits”.

DBCC results for ‘sys.sysfiles1′.

There are 2 rows in 1 pages for object “sys.sysfiles1″.

DBCC results for ‘sys.syshobtcolumns’.

There are 538 rows in 5 pages for object “sys.syshobtcolumns”.

DBCC results for ‘sys.syshobts’.

There are 78 rows in 1 pages for object “sys.syshobts”.

DBCC results for ‘sys.sysftinds’.

There are 0 rows in 0 pages for object “sys.sysftinds”.

DBCC results for ‘sys.sysserefs’.

There are 89 rows in 1 pages for object “sys.sysserefs”.

DBCC results for ‘sys.sysowners’.

There are 14 rows in 1 pages for object “sys.sysowners”.

DBCC results for ‘sys.sysprivs’.

There are 120 rows in 1 pages for object “sys.sysprivs”.

DBCC results for ‘sys.sysschobjs’.

There are 47 rows in 1 pages for object “sys.sysschobjs”.

DBCC results for ‘sys.syscolpars’.

There are 419 rows in 7 pages for object “sys.syscolpars”.

DBCC results for ‘sys.sysnsobjs’.

There are 1 rows in 1 pages for object “sys.sysnsobjs”.

DBCC results for ‘sys.syscerts’.

There are 0 rows in 0 pages for object “sys.syscerts”.

DBCC results for ‘sys.sysxprops’.

There are 0 rows in 0 pages for object “sys.sysxprops”.

DBCC results for ‘sys.sysscalartypes’.

There are 27 rows in 1 pages for object “sys.sysscalartypes”.

DBCC results for ‘sys.systypedsubobjs’.

There are 0 rows in 0 pages for object “sys.systypedsubobjs”.

DBCC results for ‘sys.sysidxstats’.

There are 93 rows in 1 pages for object “sys.sysidxstats”.

DBCC results for ‘sys.sysiscols’.

There are 207 rows in 1 pages for object “sys.sysiscols”.

DBCC results for ‘sys.sysbinobjs’.

There are 23 rows in 1 pages for object “sys.sysbinobjs”.

DBCC results for ‘sys.sysobjvalues’.

There are 93 rows in 17 pages for object “sys.sysobjvalues”.

DBCC results for ‘sys.sysclsobjs’.

There are 14 rows in 1 pages for object “sys.sysclsobjs”.

DBCC results for ‘sys.sysrowsetrefs’.

There are 0 rows in 0 pages for object “sys.sysrowsetrefs”.

DBCC results for ‘sys.sysremsvcbinds’.

There are 0 rows in 0 pages for object “sys.sysremsvcbinds”.

DBCC results for ‘sys.sysxmitqueue’.

There are 0 rows in 0 pages for object “sys.sysxmitqueue”.

DBCC results for ‘sys.sysrts’.

There are 1 rows in 1 pages for object “sys.sysrts”.

DBCC results for ‘sys.sysconvgroup’.

There are 0 rows in 0 pages for object “sys.sysconvgroup”.

DBCC results for ‘sys.sysdesend’.

There are 0 rows in 0 pages for object “sys.sysdesend”.

DBCC results for ‘sys.sysdercv’.

There are 0 rows in 0 pages for object “sys.sysdercv”.

DBCC results for ‘sys.syssingleobjrefs’.

There are 133 rows in 1 pages for object “sys.syssingleobjrefs”.

DBCC results for ‘sys.sysmultiobjrefs’.

There are 102 rows in 1 pages for object “sys.sysmultiobjrefs”.

DBCC results for ‘sys.sysdbfiles’.

There are 2 rows in 1 pages for object “sys.sysdbfiles”.

DBCC results for ‘sys.sysguidrefs’.

There are 0 rows in 0 pages for object “sys.sysguidrefs”.

DBCC results for ‘sys.sysqnames’.

There are 91 rows in 1 pages for object “sys.sysqnames”.

DBCC results for ‘sys.sysxmlcomponent’.

There are 93 rows in 1 pages for object “sys.sysxmlcomponent”.

DBCC results for ‘sys.sysxmlfacet’.

There are 97 rows in 1 pages for object “sys.sysxmlfacet”.

DBCC results for ‘sys.sysxmlplacement’.

There are 17 rows in 1 pages for object “sys.sysxmlplacement”.

DBCC results for ‘sys.sysobjkeycrypts’.

There are 0 rows in 0 pages for object “sys.sysobjkeycrypts”.

DBCC results for ‘sys.sysasymkeys’.

There are 0 rows in 0 pages for object “sys.sysasymkeys”.

DBCC results for ‘sys.syssqlguides’.

There are 0 rows in 0 pages for object “sys.syssqlguides”.

DBCC results for ‘sys.sysbinsubobjs’.

There are 0 rows in 0 pages for object “sys.sysbinsubobjs”.

DBCC results for ‘sys.queue_messages_1977058079′.

There are 0 rows in 0 pages for object “sys.queue_messages_1977058079″.

DBCC results for ‘sys.queue_messages_2009058193′.

There are 0 rows in 0 pages for object “sys.queue_messages_2009058193″.

DBCC results for ‘sys.queue_messages_2041058307′.

There are 0 rows in 0 pages for object “sys.queue_messages_2041058307″.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘TestDatabase’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 5173, Level 16, State 1, Line 1One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.Log file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDatabase_log.ldf’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.Warning: The log for database ‘TestDatabase’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. DBCC results for ‘TestDatabase’.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.DBCC results for ‘sys.sysrowsetcolumns’.There are 538 rows in 5 pages for object “sys.sysrowsetcolumns”.DBCC results for ‘sys.sysrowsets’.There are 78 rows in 1 pages for object “sys.sysrowsets”.DBCC results for ‘sysallocunits’.There are 89 rows in 1 pages for object “sysallocunits”.DBCC results for ‘sys.sysfiles1′.There are 2 rows in 1 pages for object “sys.sysfiles1″.DBCC results for ‘sys.syshobtcolumns’.There are 538 rows in 5 pages for object “sys.syshobtcolumns”.DBCC results for ‘sys.syshobts’.There are 78 rows in 1 pages for object “sys.syshobts”.DBCC results for ‘sys.sysftinds’.There are 0 rows in 0 pages for object “sys.sysftinds”.DBCC results for ‘sys.sysserefs’.There are 89 rows in 1 pages for object “sys.sysserefs”.DBCC results for ‘sys.sysowners’.There are 14 rows in 1 pages for object “sys.sysowners”.DBCC results for ‘sys.sysprivs’.There are 120 rows in 1 pages for object “sys.sysprivs”.DBCC results for ‘sys.sysschobjs’.There are 47 rows in 1 pages for object “sys.sysschobjs”.DBCC results for ‘sys.syscolpars’.There are 419 rows in 7 pages for object “sys.syscolpars”.DBCC results for ‘sys.sysnsobjs’.There are 1 rows in 1 pages for object “sys.sysnsobjs”.DBCC results for ‘sys.syscerts’.There are 0 rows in 0 pages for object “sys.syscerts”.DBCC results for ‘sys.sysxprops’.There are 0 rows in 0 pages for object “sys.sysxprops”.DBCC results for ‘sys.sysscalartypes’.There are 27 rows in 1 pages for object “sys.sysscalartypes”.DBCC results for ‘sys.systypedsubobjs’.There are 0 rows in 0 pages for object “sys.systypedsubobjs”.DBCC results for ‘sys.sysidxstats’.There are 93 rows in 1 pages for object “sys.sysidxstats”.DBCC results for ‘sys.sysiscols’.There are 207 rows in 1 pages for object “sys.sysiscols”.DBCC results for ‘sys.sysbinobjs’.There are 23 rows in 1 pages for object “sys.sysbinobjs”.DBCC results for ‘sys.sysobjvalues’.There are 93 rows in 17 pages for object “sys.sysobjvalues”.DBCC results for ‘sys.sysclsobjs’.There are 14 rows in 1 pages for object “sys.sysclsobjs”.DBCC results for ‘sys.sysrowsetrefs’.There are 0 rows in 0 pages for object “sys.sysrowsetrefs”.DBCC results for ‘sys.sysremsvcbinds’.There are 0 rows in 0 pages for object “sys.sysremsvcbinds”.DBCC results for ‘sys.sysxmitqueue’.There are 0 rows in 0 pages for object “sys.sysxmitqueue”.DBCC results for ‘sys.sysrts’.There are 1 rows in 1 pages for object “sys.sysrts”.DBCC results for ‘sys.sysconvgroup’.There are 0 rows in 0 pages for object “sys.sysconvgroup”.DBCC results for ‘sys.sysdesend’.There are 0 rows in 0 pages for object “sys.sysdesend”.DBCC results for ‘sys.sysdercv’.There are 0 rows in 0 pages for object “sys.sysdercv”.DBCC results for ‘sys.syssingleobjrefs’.There are 133 rows in 1 pages for object “sys.syssingleobjrefs”.DBCC results for ‘sys.sysmultiobjrefs’.There are 102 rows in 1 pages for object “sys.sysmultiobjrefs”.DBCC results for ‘sys.sysdbfiles’.There are 2 rows in 1 pages for object “sys.sysdbfiles”.DBCC results for ‘sys.sysguidrefs’.There are 0 rows in 0 pages for object “sys.sysguidrefs”.DBCC results for ‘sys.sysqnames’.There are 91 rows in 1 pages for object “sys.sysqnames”.DBCC results for ‘sys.sysxmlcomponent’.There are 93 rows in 1 pages for object “sys.sysxmlcomponent”.DBCC results for ‘sys.sysxmlfacet’.There are 97 rows in 1 pages for object “sys.sysxmlfacet”.DBCC results for ‘sys.sysxmlplacement’.There are 17 rows in 1 pages for object “sys.sysxmlplacement”.DBCC results for ‘sys.sysobjkeycrypts’.There are 0 rows in 0 pages for object “sys.sysobjkeycrypts”.DBCC results for ‘sys.sysasymkeys’.There are 0 rows in 0 pages for object “sys.sysasymkeys”.DBCC results for ‘sys.syssqlguides’.There are 0 rows in 0 pages for object “sys.syssqlguides”.DBCC results for ‘sys.sysbinsubobjs’.There are 0 rows in 0 pages for object “sys.sysbinsubobjs”.DBCC results for ‘sys.queue_messages_1977058079′.There are 0 rows in 0 pages for object “sys.queue_messages_1977058079″.DBCC results for ‘sys.queue_messages_2009058193′.There are 0 rows in 0 pages for object “sys.queue_messages_2009058193″.DBCC results for ‘sys.queue_messages_2041058307′.There are 0 rows in 0 pages for object “sys.queue_messages_2041058307″.CHECKDB found 0 allocation errors and 0 consistency errors in database ‘TestDatabase’.DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6. Hata mesajını almamıza rağmen, şu an veritabanı kullanılabilir durumda. Güle güle kullanın