How to Resolve SQL Server Error 5172?

-->

Sometimes when you tried to re-attach a SQL server database, you may get error message “5172: The header for file 'databaselog.ldf' is not a valid database file header. The Page Audit property is incorrect. Device activation error. The physical file name 'dataaselog.ldf' may be incorrect. New Log file was created.”

Solution: You can solve above described error message by two methods, restore from updated backup, & create a test database.

Restore from Updated Backup: If you have maintained a backup copy of the database then it is the right time to use it. In case of unavailability of updated backup; you can go with the second method that is creating a test database with same size & layout.

Create a Test Database: If you don't have any updated backup copy of your database then it is the time to create a test database with the same database size & layout. 

 

Steps to Start SQL server with test database:

• Create a test database with same size & layout.
• Shutdown SQL server database.
• Swap-in database files.
• Now restart your SQL server database.

For SQL Server 2005:

• Now your database comes up in Suspect Mode.
• Now you can export all the data in a new database or rebuild the transaction log.
• Run DBCC CHECKDB with repair_allow_data_loss.
• Bear in mind that you will lose some amount of data.

Note: If you still get error message then I suggest you to call MS SQL server support team. Waiting for solution is not a most ideal way when your business is down.

For SQL Server 2000:

If you are using SQL server 2000 database then your database will not come in suspect mode automatically. You have to bring your database in suspect mode. To put the Database in suspect mode, you need to hack the sysdatabases table so the status field has the 'bypass recovery' bit set. You can do it by reconfiguring the server to allow updates. Run the bellow script:

sp_configure "allow updates",1

reconfigure with override

go

update sysdatabases set status= 32768 where name = "mydb"

go

sp_configure "allow updates",0

reconfigure with override

go

# Now your database is in suspect mode

Perform below Steps:

• Now export all the data to a new database or rebuild the transaction log.
• Run DBCC CHECKDB with Repair_allow_data_loss.