January 8, 2002 at 2:26 pm
Following on from a similar theme in this forum I have hit a brick wall.
We had a problem on a server, our normal policy is to detach the database, delete the log file and then reattach the database. This is to counter the log-file memory problem with sql-svr 7.
So, we did it. Only this time (somebody) had decided to change the database so that it used multiple log files. Fantastic hey ?
So knowing Microsofts statement of:
"Use sp_attach_db rather than sp_attach_single_file_db to attach a database with
multiple log files. You cannot attach a database that has been created with
multiple log files without also attaching all the log files." We were stumped.
We have even tried creating a new dummy database with two log files detaching, renaming etc and trying to fool sql-svr into thinking it just had two empty log files.....guess what....didn't work....
Has anybody got any suggestions on how to re-attach this database. This is a bit serious since the clients back-up strategy leaves a lot to be desired.
Thanks
Andy P
Andy P
January 8, 2002 at 2:29 pm
I've never tried it, but what about the CREATE DATABASE using the FOR ATTACH option?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 8, 2002 at 2:40 pm
I'll try that in the morning.
I was also going to try attaching to a different server (sql-svr 2000).
I have just realised that this has been posted to the wrong forum (should have been 'Administration') appologies to all for that.
Andy P
Andy P
January 8, 2002 at 3:48 pm
January 8, 2002 at 9:23 pm
I think you have to use the attach option if there are more than 16. Otherwise sp_attach_db shoud do fine, you just keep passing it arguments.
Not totally on topic, but close is one my articles:
http://www.sqlservercentral.com/columnists/awarren/reattachingdatabases-somecodeandacontest!.asp
Andy
January 9, 2002 at 4:37 am
Well guys, looks like I'm well and truly scuppered.
SUMMARY:
DB was detached using EXEC sp_detach_db
Primary log file deleted.
Have tried to re-attach using:
1.
EXEC sp_attach_db @dbname (including combinations of @filename2/@filename3 for primary and secondary logs
2.
EXEC sp_attach_single_file_db (even though this doesn't support multiple log file DB's)
3.
CREATE DATABASE [MyDatabase] ON PRIMARY (FILENAME = 'D:\MSSQL7\Data\MyDatabase.mdf') FOR ATTACH
Have also tried using the LOG ON argument with combinations of primary and secondary logs,
(even though we don't have the primary log file anymore).
4.
Creating dummy DB of same name with same 2 trasaction logs - detaching and replacing dummy Db with previously detached DB.
5.
Moving mdf to 2000 server,
use sql-svr 2000 gui to attach,
use stored procs to attch.
EVERYTHING IS UNSUCCESSFULL
All result in (combinations of the error messages):
Server: Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name 'abcdef.LDF' may be incorrect.
Server: Msg 945, Level 14, State 1, Line 1
Database 'MyDatabase' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'MyDatabase'. CREATE DATABASE is aborted.
Any other advice greatly received.
Andy P
January 9, 2002 at 7:59 am
Ouch. I'll keep looking. The bottom line, though gets back to:
quote:
the clients back-up strategy leaves a lot to be desired.
If ever there was a warning, here it is.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 9, 2002 at 9:39 am
Ultimately, you have the answer. It's either the backup or nothing. Everything that I have seen from MS verifies what you have stated and even trying to fool it by creating new files won't work (I did try - file association problem).
Good luck!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 9, 2002 at 10:24 am
What about restoring from a backup and then detaching and replacing the last mdf with the current one? I think you are stuck, but I am reaching here.
Or not detaching, but downing SQL and making the replace.
Steve Jones
January 9, 2002 at 10:59 am
Not sure I understand - why cant you use the single attach? Once a db is detached the log files are junk - all open transactions are completed before you can detach.
Andy
January 9, 2002 at 1:46 pm
Andy - Not supported by MS when you have multiple log files. Don't have a clue as to why but, not supported.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 9, 2002 at 1:55 pm
Is your client willing to pay for the support call to Microsoft? I'm sure they've got something...
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 9, 2002 at 7:10 pm
Interesting...just never tried it I guess! I will look some, be nice to find a way.
Andy
January 14, 2002 at 1:11 pm
Suggested a MS Support call.
Let's see what happens.
Thanks All.
Andy P
Andy P
February 3, 2002 at 9:58 pm
You can use one of 3 methods to attach a set of database files:
1. sp_attach_db (works with up to 16 files)
2. sp_attach_single_file_db (works for re-creating a single log file if original is not available)
3. Create Database ... FOR ATTACH (Uses any number of files but must retain original sort order etc)
If you are using more than one log file you will not be able to reattach using method 2.
Instead use the dbcc shrinkfile with emptyfile option. Alter database to drop the extra log file.
Trying to fool SQL into thinking foriegn files are part of a database doesn't work. Don't try using older versions either.
If you are currently in the pickle, you might be able to get some assistance from Microsoft but they will tell you the same as above.
If that is the case spend your time trying to restore the backup.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply