March 13, 2009 at 2:32 pm
We sell a software system that uses SQL Server 2005 database to industrial customers that may or may not have IT let alone a competent dba ... just so you know I'm not talking about an in house database.
I had a customer make a backup to DVD and send it to me. When I restore customer dbs, I have to change the file paths because theirs are on D and E (log) and I only have a C drive. Have done this a thousand times.
With this particular customer db, there are more files than there are supposed to be: the usual four data files and TWO logs. In the Restore Database dialog on the Options tab, they show in list as "Original File Name" of "TransactionLog" and "TransactionLog1". I got an error trying to restore this db which I finally circumvented by deleting existing db of same name then restoring (overwriting the existing db would not work).
I'd like to know how this customer's db got two transaction logs, but that's just so I can sleep at night.
More important to me is, how can I get rid of one of them?
March 18, 2009 at 1:32 pm
What's the error message that you receive while restoring?
MJ
March 18, 2009 at 2:01 pm
You can add a transaction log to any DB. Can be done by going into database properties in Management Studio, can be done by issuing commands.
Doesn't server a lot of purpose, but it can be done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2009 at 4:09 pm
The error message:
System.Data.S1qlClient.SqlError: File 'c:\Log\rp_log.LDF' is claimed by 'TransactionLog1'(6) and
'TransactionLog' (2). The WITH MOVE clause can be used to relocate one or more files.
Restore Database, Options page:
Restore Options
[x] Overwrite the existing database
[ ]
[ ]
[ ]
Original File name Restore As
PrimaryDatac:\database\rp_primary.mdf
ConfigDatac:\database\rp_config.ndf
SampleDatac:\database\rp_sample.ndf
RealtimeDatac:\database\rp_realtime.ndf
TransactionLogc:\database\rp_log1.ldf
TransactionLog1c:\log\rp_log.ldf
Note that the c:\database\rp_log1.ldf is the mysterious newcomer, not c:\log\rp_log.ldf
It is possible someone created the extra log manually as described in the reply, but unlikely. I know a few customer sites have played with replication wizard and accidentally added timestamp columns to some of our tables, so anything is possible.
March 19, 2009 at 6:46 am
Some people are under the mistaken impression that adding an extra log file improves performance. I've seen that come up a few times. Someone may have thought that and added it for that reason.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 20, 2009 at 9:27 am
Try this:
Drop the existing database and then do your restoration
Or
Would be good if you post your restore statement here.
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply