Repairing DB In Emergency Mode (By-Pass Recovery Mode)
How to attach the MDF file using query analyzer?
How to bring database back on operation when the LDF file is damaged?
How to By-Pass Recovery Mode and bring the database online back through ‘Emergency Mode’?
Solution:
If we need to ignore the current LDF file and if the database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.
If we need to ignore the current LDF file and if the database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.
- Create a new database with the same name and same MDF and LDF files
- Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
- Start SQL Server.
- Now your database will be marked suspect.
sp_configure 'allow updates',1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where dbid=DB_ID('DB_NAME')
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where dbid=DB_ID('DB_NAME')
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
- Restart sql server. Now the database will be in Emergency mode
- Now execute the undocumented DBCC to create a log file.
dbcc rebuild_log('DB_NAME','G:\SQL_Server_DBs\MSSQL$BDC_BRS003\Data\User Databases\DB_NAME_log.ldf')
go
dbcc checkdb('DB_NAME') -- to check for errors
go
• Now run the below command.
Execute sp_resetstatus DB_NAME
• Restart SQL server and see the database is online.
URL: http://mssql-tech.blogspot.com/2011/11/how-to-attach-sql-2000-database-mdf.html
Other References:
http://oldlight.wordpress.com/2007/05/04/hello-world/ http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/289d7bb97da73312?hl=en&lr&ie=UTF-8&rnum=1&prev=/groups?sourceid%3Dnavclient%26q%3Djasper%2Bsmith%2Battach%2Blog&pli=1 http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/13235/suspect-database-is-sql-2000 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78376 http://solution-mdf-file-corruption.blogspot.com/2009/12/how-to-repair-mdf-files-not-detached.html
The MDF files of SQL database may get corrupt due to some problem in the database. If such a situation arises, the user can use the software which recovers the lost MDF file. But before choosing the software, make sure that it is efficient enough to recover the unique keys, primary keys, indexes, stored procedures, views etc from your corrupt MDF file.you can try this application to repaie sql database.
ReplyDeletehttp://www.recoverydeletedfiles.com/sql-database-recovery-software.html
You can also take the help of this link http://www.sqlserverlogexplorer.com/how-to-attach-mdf-file-without-ldf-file/ it shows step by step method to attach mdf file without ldf (with screenshots)
ReplyDeletehttps://programmingtutorialsscript.blogspot.com
ReplyDeleteludiaKas_ya Ana Brown https://wakelet.com/wake/H540nISGwafWKLXzdvBqi
ReplyDeleteabomuntran
Mcerritral_koKansas City Dana Jones Adobe Acrobat Pro DC
ReplyDeleteAutodesk Maya
CyberLink YouCam
nyatilimna
Attaching a SQL 2000 database MDF involves using SQL Server Management Studio. Using Sony Tv Launch SSMS, connect to the server, right-click on "Databases," choose "Attach," then add the MDF file.
ReplyDelete