Recover database from mdf

Question

Post reply

Recover database from mdf

shailendra-251422

Ten Centuries

Points: 1111
More actions
March 16, 2006 at 3:23 am

#365568

Using SQL 2000.
I deleted the ldf file to save space. Now the database is in suspect mode. How can I make the database functional again - I have only the mdf file. I had stopped the server and deleted the ldf file.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

smackie1973 SSC Enthusiast Points: 136 More actions · Answer 1

Hi

Did some digging and found the command: -

sp_attach_single_file_db

Did a check in Books Online and it looks like it may work, although i haven;t tried it myself.

Scott

smackie1973 SSC Enthusiast Points: 136 More actions · Answer 2

Just tested this and it works.

You will have to detach the database first using sp_detach_db ''

Then run

sp_attach_single_file_db '', ''

You will get a message saying that the file name of the log file may be incorrect. It will then create a new one. On my test box it created the log file in the same dir as the MDF, so if you want to move it to a different dir, just detach it and reattach it using sp_attach_db.

Scott

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

if your log files are getting too big, you need to back them and/or maybe put the database into simple recovery ( I assume you're not on a production system )

sometimes the process of detach db , delete log, attach db can be quickest method to get a new small log.

Can I suggest not deleting database files 🙂

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

leonardo Abejar-295368 Old Hand Points: 330 More actions · Answer 4

dude! try this...it worked for me....

Make sure you have a copy of the mdf before starting

this. Also replace the relevent database,drive letters and filenames with your particulars as this answer was for a specific case.

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

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

3) Stop SQL Service

4) Delete the fake_Data.MDF and copy DBNAME_data.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 :

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)

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 Analyser

(At this stage you can actually access the database so you could use

DTS or bcp to move the data to another database .)

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','DBNAME_data'

go

alter database DBNAME_data

MODIFY FILE(NAME='datafilename', NEWNAME = 'DBNAME_data')

go

alter database DBNAME_data

MODIFY FILE(NAME='logfilename', NEWNAME = 'DBNAME_data')

go

dbcc checkdb('DBNAME_data')

go

sp_dboption 'DBNAME_data','dbo use only','false'

go

use DBNAME_data

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 the

database properly and then renaming the files and reattaching

it

14) Run the following in QA

sp_detach_db DBNAME_data

--now rename the files then reattach

sp_attach_db 'DBNAME_data','h:\db_name.mdf','h:\db_name.ldf'

Let me know if you have any problems....i'll be glad to help.....

**courtesy of Jasper Smith (SQL Server MVP)http://www.sqlpass.org

Tommy Rigler Grasshopper Points: 11 More actions · Answer 5

I have a stored procedure that runs nightly. It truncates the log and also shrinks the database. It is as follows:

/*----------------------------------------------

Truncates Transaction Log and Shrinks DB

Scheduled to run at 2:30 AM daily after

Database backup which runs at 2:00 AM

Last Modified 11/08/2005 - TR

----------------------------------------------*/

CREATE PROCEDURE [dbo].[adminMaint_TruncateLog]

AS

BACKUP LOG ameddcsproject WITH no_log

DBCC SHRINKDATABASE

( ameddcsproject, 1, TRUNCATEONLY )

GO

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

Just to sure you are aware that shrinking mdf and ldf files will increase the likelyhood of file level fragmentation.

Note that shrinking a mdf file will tend to increase the number of mixed extents and promote internal fragmentation.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/