Yesterday, my colleague gave you a database of problems and asked me to fix it. Because the customer needs this database, this database has only one MDF file and one LDF file,
When I append the database error, the database is SQL2005
Attached is a corrupted database file:
Because before in the Forum also met, so according to the Forum method to solve, the result is still not
Move the LDF file to another place, and then use the following SQL statement to rebuild the transaction log file when attached
My database files are placed under the C:\Users\Administrator\Desktop\ new folder directory
Copy Code code 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 content:
Copy Code code as follows:
1 file activation failed. Physical file name ' D:\MSSSQL\Data\GPOSDB_log. LDF ' may not be correct.
2 The log cannot be rebuilt because the database is not completely shut down.
3 Message 1813, Level 16, State 2, line 1th
4 Unable to open the new database ' Gposdb '. CREATE DATABASE aborted.
I'm going to try this article right now.
Http://www.blogjava.net/kent/articles/200991.html
Create a new gposdb empty library, and then stop the SQL service
Delete the log file gposdb_log.ldf of the database you just generated
Overwrite the database data file you just generated with the Gposdb.mdf file you want to recover gposdb.mdf
Then put the problematic Gposdb.mdf file in D, because my new GPOSDB database is placed in D-disk
Start the SQL service
The article says it will show the database suspect, but my no doubt
Set database allow direct operating system table
Enter the following SQL statement in the SSMS
Copy Code code as follows:
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with OVERRIDE
Go
Set GPOSDB as Emergency Repair mode
Copy Code code 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 the error
Copy Code code as follows:
1 message 259, Level 16, State 1, line 1th
2 does not allow ad hoc updates to the system directory.
Attempt to rebuild the log, but the syntax is wrong, it is estimated that the article is SQL2000
Copy Code code as follows:
1 DBCC rebuild_log (' Gposdb ', ' D:\GPOSDB_log.ldf ')
2 Go
1 message 2526, Level 16, State 3, line 1th
2 DBCC statement error. Consult your documentation for the correct DBCC syntax and options.
A check is indeed
--* DBCC Rebuildlog
--Rebuilding SQL Server 2000 transaction log files
In fact, the first step "set the database to allow direct operating system table" is suspected is not SQL2000, because SQL2005 or later can not modify the system table
Finally, put the transaction log file into D and use the following SQL statement to fix it or not.
Copy Code code as follows:
ALTER DATABASE [GPOSDB] SET Emergency
Go
ALTER DATABASE gposdb SET single_user
Go
DBCC CheckDB (Gposdb, Repair_allow_data_loss)
Go
Copy Code code as follows:
Message 5173, Level 16, State 1, line 2nd
One or more files do not match the database's primary file. If you are trying to attach a database, retry the operation with the correct file. If this is an existing database, the file may be corrupted and should be restored from backup.
The log file ' D:\GPOSDB_log.ldf ' does not match the primary file. The file may be from another database, or the log may have been rebuilt before.
Message 5123, Level 16, State 1, line 2nd
Attempt to open or create a physical file ' D:\MSSSQL\Data\GPOSDB_log. LDF ', CREATE FILE encountered operating system error 3 (the system could not find the specified path.) )。
Message 5024, Level 16, State 2, line 2nd
The entry for the primary log file could not be found in sysfiles1. Unable to rebuild the log.
Message 5028, Level 16, State 2, line 2nd
The system cannot activate enough databases to rebuild the log.
Gposdb the DBCC results.
CHECKDB found 0 allocation errors and 0 consistency errors in the database ' Gposdb '.
Message 7909, Level 20, State 1, line 2nd
Emergency mode repair failed. You must restore from the backup.
