How to solve the 5173 error in the 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.

Attached damaged database files:

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.

Copy codeThe Code is as follows: USE [master]
GO
Create database [AdventureWorks2012] ON
(FILENAME = n'c: \ Users \ Administrator \ Desktop \ New Folder \ GPOSDB. mdf ')
FOR ATTACH_REBUILD_LOG
GO

Error message:Copy codeThe Code is as follows: 1. failed to activate the file. The physical file name 'd: \ MSSSQL \ Data \ gposdb_log.ldf' may be incorrect.
2. Logs cannot be regenerated because the database is not completely closed.
3 message 1813, level 16, status 2, 1st rows
4. The new database 'gposdb' cannot be opened '. The create database is aborted.

I will try again following this article.

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

Copy codeThe Code is as follows: USE master
Go
Sp_configure 'Allow updates', 1
Go
RECONFIGURE WITH OVERRIDE
Go

Set GPOSDB to Emergency Repair ModeCopy codeThe Code is as follows: alter database [GPOSDB] SET EMERGENCY
GO
Alter database gposdb set SINGLE_USER
GO

UPDATE sysdatabases
SET status =-32768
WHERE dbid = DB_ID ('gposdb ')
GO

But an error is reported.Copy codeThe Code is as follows: 1 message 259, level 16, status 1, 1st lines
2. Do not allow 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.Copy codeThe Code is as follows: 1 DBCC rebuild_log ('gposdb', 'd: \ gposdb_log.ldf ')
2 GO
1. 2526 messages, level 16, status 3, and 1st rows
2 DBCC statement error. Please refer to the documentation for the correct DBCC syntax 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.

Copy codeThe Code is as follows: alter database [GPOSDB] SET EMERGENCY
GO
Alter database gposdb set SINGLE_USER
GO

DBCC CheckDB (GPOSDB, REPAIR_ALLOW_DATA_LOSS)
GO

Copy codeThe Code is as follows: Message 5173, level 16, status 1, 2nd lines
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.
The log file 'd: \ gposdb_log.ldf' does not match the main file. The file may come from another database, or the log may have been regenerated before.
Message 5123, level 16, state 1, 2nd rows
When you try to open or CREATE a physical FILE 'd: \ MSSSQL \ Data \ GPOSDB_log.LDF ', the create file encounters an operating system error 3 (the system cannot find the specified path .).
Message 5024, level 16, status 2, 2nd rows
The entry corresponding to the main log file cannot be found in sysfiles1. Logs cannot be rebuilt.
Message 5028, level 16, status 2, 2nd rows
The system cannot activate enough databases to recreate logs.
The DBCC result of GPOSDB.
CHECKDB finds 0 allocation errors and 0 consistency errors in the database 'gposdb.
Message 7909, level 20, status 1, 2nd rows
Failed to repair in emergency mode. You must restore data from the backup.