June 22, 2012 at 12:18 am
Hi Guys,
I am gone through Many Topics online to find out how to recover LOG file in case its Crashed or Even deleted.
but I am Still Curious if any new modifications has been done in SQL Server 2008 to recover the Transactional Files Faster in case its Lost\Deleted\CraShed.
The Process that I know as of now is Generic-
-----------------------------------------
Stop SQL service, rename DbLive.mdf to DbBad.mdf.
started SQL service, created fake DbLive db (with log etc)
Stopped SQL service
Deleted DbLIve.mdf
Renamed DbBad.MDF to DbLive.MDF
Started SQL service.
Ran following script:
*/
ALTER DATABASE DbLive SET EMERGENCY
sp_dboption 'DbLive', 'single user', 'true'
DBCC CHECKDB ('DbLive', REPAIR_ALLOW_DATA_LOSS)
sp_dboption 'DbLive', 'single user', 'false'
==========
What Else can be done ??
Is there anything that can be done through SSMS??
thx in advance
June 22, 2012 at 1:41 am
Restore from backup is the safest and often fastest.
Your 'solution' contains deprecated functionality (sp_dboption) and may well result in data loss (CheckDB with REPAIR ALLOW DATA LOSS)
Any 'solution' that suggests rebuilding the log is a last resort for the cases when there is no good backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2012 at 3:49 am
Gail-- Thanks for Repsonding
your reply Actually Suggests that we Need to Do a Complete restore and there is no Other way to Sort out things .
Another Way of fixing is as Follows:
If you want to attach a MDF without LDF you can follow the steps below It is tested and working fine
1.Create a new database with the same name and same MDF and LDF files
2.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.
3.Start SQL Server
4.Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up
Sp_configure "allow updates", 1 go Reconfigure with override GO Update sysdatabases set status = 32768 where name = "BadDbName" go Sp_configure "allow updates", 0 go Reconfigure with override GO
1.Restart sql server. now the database will be in emergency mode
2.Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.
(replace the dbname and log file name based on ur requirement)
1.Execute sp_resetstatus
2.Restart SQL server and see the database is online.
UPDATE: DBCC REBUILD_LOG does not existing SQL2005 and above. This should work:
USE [master] GO CREATE DATABASE [Test] ON (FILENAME = N'C:\MSSQL\Data\Test.mdf') FOR ATTACH_REBUILD_LOG GO
June 22, 2012 at 5:32 am
Jai-SQL DBA (6/22/2012)
Another Way of fixing is as Follows:If you want to attach a MDF without LDF you can follow the steps below It is tested and working fine
Nope, that's not going to work on SQL 2005, 2008 or 2012. It's most certainly not 'tested and working fine'. Also Attach_rebuild_log won't work if the database was not cleanly shut down
Even if it did work, it's still going to cause data loss, potential consistency or integrity problems and may not even work at all.
As I said in my first post, restore from backup is the safest and often fastest. Any 'solution' that suggests rebuilding the log is a last resort for the cases when there is no good backup.
Now, if you make a habit of not having backups for critical production databases and hence need these kinds of last resorts, maybe time to check maintenance routines, not how to recover from situations you should never end up in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2012 at 5:36 am
Appreciate your Support Here...Thanks for the Help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply