Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_attach_db without log files Expand / Collapse
Author
Message
Posted Wednesday, September 21, 2005 2:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 27, 2013 8:10 AM
Points: 69, Visits: 101
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.
Post #221913
Posted Wednesday, September 21, 2005 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
did you try sp_attach_single_file_db ?

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #221922
Posted Wednesday, September 21, 2005 2:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 27, 2013 8:10 AM
Points: 69, Visits: 101
Identical error...
Post #221928
Posted Wednesday, September 21, 2005 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
What if you use EM to attach the db and you fill out the logfiles ?

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #221935
Posted Wednesday, September 21, 2005 2:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 27, 2013 8:10 AM
Points: 69, Visits: 101
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....
Post #221936
Posted Wednesday, September 21, 2005 3:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480

did you check usp_AttachDBWithMissingLogFile ?

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

 



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #221942
Posted Wednesday, September 21, 2005 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #221943
Posted Wednesday, September 21, 2005 3:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 27, 2013 8:10 AM
Points: 69, Visits: 101
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.
Post #221955
Posted Wednesday, September 21, 2005 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 23, 2010 8:50 PM
Points: 33, 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

Post #222101
Posted Wednesday, September 21, 2005 8:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480

 

try

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

?



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #222102
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse