SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_attach_db without log files


sp_attach_db without log files

Author
Message
Stephen Knott
Stephen Knott
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 123
Hi,

I think I have done something incredibly stupid.

We are taking backups of a clients Production system (SQL 2000 database). We will use it for testing / development.

I restored the database successfully on our server. I then noticed that the log files were huge, so decided to detach the db, delete the log file, and re-attach, to obtain a smaller log file.

I have done this many times, and thought it would be seamless.

Unfortunatley there were 2 log files. I deleted both of them.

Now I cannot attach the db. I have deleted the backup, as I had the db setup on our server.

I cannot attach the db as i get the following errors...

Device activation error. The physical file name 'H:\data\ARV_log.ldf' may be incorrect.
Device activation error. The physical file name 'H:\data\ARV_log_1.ldf' may be incorrect.

I have found comments on the web stating that this is expected behaviour when more than 1 log file existed ?

Is there any way to attach the db, and create 2 empty log files. I have tried creating a new db, with both log files, and using those (Same DB name) , but SQL sees them as files for a different db..

Any idea's ?

It is a mission to obtain a new copy of the backup, as the client needs to courier the backup on hard disk, as it is large.
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12348 Visits: 8930
did you try sp_attach_single_file_db ?

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Stephen Knott
Stephen Knott
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 123
Identical error...
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12348 Visits: 8930
What if you use EM to attach the db and you fill out the logfiles ?

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Stephen Knott
Stephen Knott
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 123
EM gives the same message....

I read here... http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp

that if you have 2 files it will give an error... but was hoping there was another solution....
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12348 Visits: 8930

did you check usp_AttachDBWithMissingLogFile ?

http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12348 Visits: 8930

I also found this in my archive :

-- DBCC rebuild_log (dbname [, filename])
-- -- -- -- Create new database with same name, stop SQL Server, replace the MDF file from original database file
-- -- -- -- and restart SQL Server to see whether your database is back in suspect mode.
-- -- -- -- If it does, reset the database to emergency mode and run DBCC Rebuild_Log to recreate the log file.
-- -- -- -- Once database is back to normal, run DBCC checkdb to ensure there is no integrity error,
-- -- -- -- otherwise, you have to BCP the data out.
-- -- --
Allen Cui www.sqlservercentral.com did an article on this "Undocumented DBCC Command REBUILD_LOG"



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Stephen Knott
Stephen Knott
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 123
Used the DBCC Rebuild_Log idea, and it worked like a charm.

Thanks.

The stored proc above would have worked too, but since I had many mdf files... it would have been more work.. plus seems more risky in the system tables etc...

Thanks.
Al Heckers
Al Heckers
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 2

I have a similar situation. I have a database data file named Dorian Event Archiver_Data.mdf and no corresponding log file. (it was deleted, no backup) I am trying to reattach the database with and have SQL create a new trans log. I tried the suggestion above:

DBCC rebuild_log (Dorian Event Archiver, Dorian Event Archiver_Log.ldf)

but get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Event'.

Ideas?

-Al


ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12348 Visits: 8930

try

DBCC rebuild_log ('Dorian Event Archiver', 'yourdrivepath\Dorian Event Archiver_Log.ldf')

?



Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search