Error 5173 in SQL Server database

Error 5173 in SQL Server database

Yesterday, my colleague gave you a problematic database and asked me to fix it because the customer needed this database. This database only has one MDF file and one LDF file,

When I attach a database, an error is reported. The database is sql2005.

Attach corrupted database file: http://files.cnblogs.com/lyhabc/Data.zip

 

Because I have encountered this problem in the Forum before, I still cannot solve the problem according to the solution of the Forum.

Move the LDF file to another place, and then use the following SQL statement to re-create the transaction log file when appending it.

My database files are stored in the C: \ Users \ Administrator \ Desktop \ new folder directory.

 1   Use   [  Master  ]  2    Go  3    Create  Database   [  Adventureworks2012  ]   On   4 (Filename = N '  C: \ Users \ Administrator \ Desktop \ New Folder \ gposdb. MDF  '  )  5     For  Attach_rebuild_log  6   Go 

Error message:

1Failed to activate the file. Physical file name'D: \ msssql \ data \ gposdb_log.ldf'It may be incorrect.2 Because the database is not completely closed, logs cannot be regenerated.3Message1813, Level16, Status2, No1Line4Unable to open new database'Gposdb'.Create DatabaseAbort.
 

 

 

 

According to thisArticleTry again

Http://www.blogjava.net/kent/articles/200991.html

Create a new empty gposdb database and stop the SQL service.

Delete the log file gposdb_log.ldf of the database just generated

Overwrite the generated database data file gposdb. MDF with the gposdb. MDF file to be restored.

Then place the problematic gposdb. MDF file on the d disk, because the newly created gposdb database is placed on the d disk.

Start SQL Service

The article said that it would show database doubts, but I did not show doubts

Set the database to allow direct operation of system tables

Enter the following SQL statement in SSMs

1 UseMaster2 Go3Sp_configure'Allow updates',1 4 Go5 Reconfigure WithOverride6 Go
 

Set gposdb to Emergency Repair Mode

 1   Alter   Database  [  Gposdb  ]   Set  Emergency  2   Go  3   Alter   Database Gposdb Set  Single_user  4   Go  5   6   Update Sysdatabases  7   Set Status =   -  32768  8   Where Dbid =   Db_id ( '  Gposdb  '  )  9   Go 

But an error is reported.

1Message259, Level16, Status1, No1Line2You cannot perform ad hoc updates to System directories.
 

Try to recreate the log, but the syntax is incorrect. It is estimated that the article was from SQL2000.

 
1 DBCCRebuild_log ('Gposdb','D: \ gposdb_log.ldf')2 Go
 

1Message2526, Level16, Status3, No1Line2 DBCCStatement error. Please refer to the documentation for the correctDBCCSyntax and options.
 

Check whether it is

-- * DBCC rebuildlog
-- Re-create the SQL Server 2000 Transaction Log File

 

In fact, in the first step, "setting the database to allow direct operation of the system table", it is suspected that it is SQL2000, because sql2005 or later can no longer modify the system table.

Finally, put the transaction log file on the d disk, and then use the following SQL statement to fix the problem.

 1   Alter   Database   [ Gposdb  ]   Set  Emergency  2   Go  3   Alter   Database Gposdb Set  Single_user  4   Go  5   6   DBCC  Checkdb (gposdb, repair_allow_data_loss) 7   Go 

 

 1 Message 5173 , Level 16 , Status 1 , No 2  Line  2   One or more files do not match the master file of the database. If you try to attach a database, use the correct file to retry the operation. If this is an existing database, the file may be corrupted and should be restored from the backup.  3 Log Files '  D: \ gposdb_log.ldf '  Does not match the master file. The file may come from another database, or the log may have been regenerated before.  4 Message 5123 , Level 16 , Status 1 , No 2  Line  5 Try to open or create a physical file '  D: \ msssql \ data \ gposdb_log.ldf  ' , Create  File Operating System Error 3  (The system cannot find the specified path .).  6 Message 5024 , Level 16 , Status 2 , No 2  Line  7   The entry corresponding to the main log file cannot be found in sysfiles1. Logs cannot be rebuilt.  8 Message 5028 , Level16 , Status 2 , No 2  Line  9   The system cannot activate enough databases to recreate logs.  10 Gposdb's DBCC  Result.  11 Checkdb in the database '  Gposdb  ' Found 0 Allocation errors and0  Consistency errors.  12 Message 7909 , Level 20 , Status 1 , No 2  Line  13 Failed to repair in emergency mode. You must restore data from the backup.

 

 

Finally, I gave up ~~ Hope you can help me with the solution. Thank you, O (∩ _ ∩) O.