Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Microsoft SQL Server: Setup and Administration FAQ

Recovery

Recovering when LDF is lost/corrupted
Posted: 23 Mar 06

First attempt to use sp_attach_single_file_db

CODE

USE master;
GO
EXEC sp_detach_db @dbname = 'AdventureWorks';
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',
    @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf';

if this doesnt work then try the following:
(Ive used this script myself and found it a life saver, I believe it may have come from Jasper Smith, although I cant find an original ref)


1) Make sure you have a copy of your.MDF

2) Create a new database called fake (default file locations)

3) Stop SQL Service

4) Delete the fake_Data.MDF and copy your.MDF to where fake_Data.MDF used to be and rename the file to fake_Data.MDF

5) Start SQL Service

6) Database fake will appear as suspect in EM

7) Open Query Analyser and in master database run the following :

CODE

     sp_configure 'allow updates',1
     go
     reconfigure with override
     go
     update sysdatabases set
        status=-32768 where dbid=DB_ID('fake')
     go
     sp_configure 'allow updates',0
     go
     reconfigure with override
     go
This will put the database in emergency recovery mode
8) Stop SQL Service

9) Delete the fake_Log.LDF file

10) Restart SQL Service

11) In QA run the following (with correct path for log)

CODE

    dbcc rebuild_log('fake','h:\fake_log.ldf')
    go
    dbcc checkdb('fake') -- to check for errors
    go
12) Now we need to rename the files, run the following (make sure there are no connections to it) in Query Analyzer (At this stage you can actually access the database so you could use DTS or bcp to move the data to another database).

CODE

    use master
    go
    sp_helpdb 'fake'
    go
    /* Make a note of the names of the files , you will need them
    in the next bit of the script to replace datafilename and
    logfilename - it might be that they have the right names  */
    sp_renamedb 'fake','your'
    go
    alter database your
        MODIFY FILE(NAME='datafilename', NEWNAME = 'your_data')
    go
    alter database PowerDVD301
        MODIFY FILE(NAME='logfilename', NEWNAME = 'your_Log')
    go
    dbcc checkdb('your')
    go
    sp_dboption 'your','dbo use only','false'
    go
    use your
    go
    sp_updatestats
    go

13) You should now have a working database. However the log file will be
small so it will be worth increasing its size. Unfortunately your files will
be called fake_Data.MDF and fake_Log.LDF but you can get round this by
detaching thedatabase properly and then renaming the files and reattaching it.

14) Run the following in QA

CODE

    sp_detach_db your
    --now rename the files then reattach
    sp_attach_db 'your','your.mdf','your.ldf'

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close