Error 5173 in SQL Server database

5173 error in SQL Server database my colleague gave you a problematic database yesterday and asked me to fix it because the customer needed the database. The database only had one mdf file and one ldf file, when I attach a database, an error is reported. The database is SQL2005 with a corrupted database file: files. cnblogs. comlyhab

5173 error in SQL Server database my colleague gave you a problematic database yesterday and asked me to fix it because the customer needed the database. The database only had one mdf file and one ldf file, when I append the database error, the database is SQL2005 attached with a corrupt database file: http://files.cnblogs.com/lyhab

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 GO3 create database [AdventureWorks2012] ON 4 (FILENAME = n'c: \ Users \ Administrator \ Desktop \ New Folder \ GPOSDB. mdf ') 5 FOR ATTACH_REBUILD_LOG 6 GO

Error message:

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, row 4 cannot open the new database 'gposdb '. 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

1 USE master2 go3 sp_configure 'allow updates', 1 4 go5 RECONFIGURE WITH OVERRIDE6 go

Set GPOSDB to Emergency Repair Mode

1 ALTER DATABASE [GPOSDB] SET EMERGENCY 2 GO3 ALTER DATABASE GPOSDB SET SINGLE_USER4 GO5 6 UPDATE  sysdatabases7 SET     status = -327688 WHERE   dbid = DB_ID('GPOSDB')9 GO

But an error is reported.

1 message 259, level 16, status 1, 1st rows 2 do not allow ad-hoc updates to the system directory.

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

1 DBCC rebuild_log('GPOSDB','D:\GPOSDB_log.ldf')2 GO

1 message 2526, level 16, status 3, 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.

1 ALTER DATABASE [GPOSDB] SET EMERGENCY 2 GO3 ALTER DATABASE GPOSDB SET SINGLE_USER4 GO5 6 DBCC CheckDB (GPOSDB, REPAIR_ALLOW_DATA_LOSS)7 GO

1 message 5173, level 16, status 1, 2nd rows 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. The log file '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. When you attempt to open or create a physical file 'd: \ MSSSQL \ Data \ GPOSDB_log.LDF for message 5123, level 16, status 1, and line 5, create file encounters OS error 3 (the system cannot find the specified path .). 6 Message 5024, level 16, status 2, 2nd rows 7 the entry corresponding to the master log file cannot be found in sysfiles1. Logs cannot be rebuilt. 8 Message 5028, level 16, status 2, 2nd rows 9 the system cannot activate enough databases to recreate logs. 10 gposdb dbcc results. 11 CHECKDB found 0 allocation errors and 0 consistency errors in the database 'gsdb. 12 message 7909, level 20, status 1, 2nd rows 13 emergency mode repair failed. You must restore data from the backup.

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