sp_attach_db without log files
-
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.
-
did you try sp_attach_single_file_db ?
Johan
Learn to play, play to learn !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[/url]- 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
-
Identical error...
-
What if you use EM to attach the db and you fill out the logfiles ?
Johan
Learn to play, play to learn !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[/url]- 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
-
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....
-
did you check usp_AttachDBWithMissingLogFile ?
http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp
Johan
Learn to play, play to learn !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[/url]- 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
-
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 http://www.sqlservercentral.com did an article on this "Undocumented DBCC Command REBUILD_LOG"
Johan
Learn to play, play to learn !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[/url]- 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
-
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.
-
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
-
try
DBCC rebuild_log ('Dorian Event Archiver', 'yourdrivepath\Dorian Event Archiver_Log.ldf')
?
Johan
Learn to play, play to learn !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[/url]- 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
-
Thanks.
Got the following result:
Server: Msg 2520, Level 16, State 4, Line 1
Could not find database 'Dorian Event Archiver'. Check sysdatabases.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Maybe I'm not understanding this, but how will DBCC rebuild_log work if the DB is not currently attached?
-Al
-
You _will_ have to follow the procedure described in Allan Cui's article !
Johan
Learn to play, play to learn !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[/url]- 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
-
Where is the article? - I don't see it
-
OK, it's not an article, just a post - I found it.
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.
Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
-- Restart SQL Server at this point.
DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')
/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/
ALTER DATABASE MyDatabase SET MULTI_USER
GO
-- Set database options and recovery model as desired.
GO
-
I've completed the procedure down to where it says to run DBCC CHECKDB at which point I get some errors back. If I try to run it with any of the repair options, I get a message saying DB must be in SINGLE_USER mode. If I try to run ALTER DATABASE Dorian Event Archiver SET SINGLE_USER, I get the following:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SET'.
and I've tried ALTER DATABASE 'Dorian Event Archiver' SET SINGLE_USER and get:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Dorian Event Archiver'.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply