How to reattach an MDF with a TWIST

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to reattach an MDF with a TWIST

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-21 : 10:34:22

Sy writes "Hello,
I have a SQL2K MDF that I cannot figure out how to reattach because :-

1.) sp_detach_db was NOT used to cleanly dismount.
2.) The LDF has been deleted and is unavailable.

So my question is - How do I reattach an MDF when it was NOT detached firstly and the corresponding LDF is missing.

I have looked everywhere for a full day and cannot find the answer to this seemingly straightforward procedure.

Thanking you in advance,
Sy"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-08-21 : 11:00:47

Perhaps you could recreate the DB as it was created originally. Then swap the newly created file with your oddly detached one.

An ldf would've been created anyway.

Even if you attach an mdf file on its own the ldf will create itself automatically at 1MB i believe

Paul

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-21 : 11:09:26

You could try sp_attach_single_file_db.

Look up the details in the Books Online

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-21 : 12:35:32

If sp_attach_single_file_db fails as it sometimes does you can try the following technique (you need to change the database name and filenames as these were for a specific recovery)

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

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

3) Stop SQL Service

4) Delete the fake_Data.MDF and copy PowerDVD301_2_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','PowerDVD301'
go

alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME = 'PowerDVD301_Data')
go

alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'PowerDVD301_Log')
go

dbcc checkdb('PowerDVD301')
go

sp_dboption 'PowerDVD301','dbo use only','false'
go

use PowerDVD301
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 PowerDVD301

--now rename the files then reattach

sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources