February 27, 2011 at 6:07 pm
Hi All,
I have encountered very strange issue. It forced me to questioning my knowledge.
I have 3 different sql instances on a server. Data/log files for each instance stored at different location. One of my databases called aaa on instance1 has mdf at M:\instance1\aaa.mdf and log at L:\instance1\aaa.ldf. Today I receive an e-mail notification that t-log backup failing on Instance2. When I looked at the logs found out there is database aaa on instance2 referring to mdf/ldf of aaa database of instance1. In nut shell there is aaa database on instance1 and instance2 and both were using same mdf and ldf. Just wondering is it possible to have a database with same name in two different instances and with same mdf/ldf file.... looked at the backup this database was there on both instances since 6th feb 2011 *(when we migrate instance1 and 2 to new server).
Anyone please show me some direction how it can happen. When i try to attach same db on different server with same mdf/ldf getting an error. Please help i am soooooooooooo confused.
Thanks
February 27, 2011 at 8:37 pm
Uh, ya. That's not normal behavior. You should bet the error you describe when attempting to attach in-use files. Almost sounds like the migration restored over top of the other data files, perhaps if you had the instance1 services shut down. At any rate, this is not good and needs to be corrected.
Now, before doing anything, I would double check my work to ensure that the file paths for DIFFERENT instances do in fact point to the same data files. If so, I would select from each instance to ensure the data is in fact identical. If so, then I would do the following:
First, take a full backup to ensure the data is saved. Better try on both databases to separate .bak files just to be sure.
Then, you may want to detach one at a time so both are detached. This will clean up your master database which tracks the database names, file paths, etc. Then, do a dbcc checkdb on each master db to ensure there are not larger problems.
Then restore each full backup, one at a time to their respective locations. Ensure the data files are specifically located properly. So use WITH MOVE or in SSMS, go to the advanced Options tab when restoring and ensure the paths are correct before restoring.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 27, 2011 at 8:50 pm
Thanks for your reply Jim.
i already detached the db from instance2 because it shouldn't be there. but i can't understand how it happened. and on the top everything was normal from last 20days. and last nigth all of sudden t-log backup start failing on Instance2. the database wasn't in use at all. my worry is how this happend? as far as i know it is not possible to have 2 live databases pointing to same mdf and ldf or it us possible?
February 27, 2011 at 10:19 pm
Oh good. Glad you are back up and going.
It is not possible. Under normal circumstances. Is a process/job backing up and restoring to the other instance each night? If so, you may want to ensure a 'with move' is used to specify a new path. Did someone restore a copy? Even still, this is not normal behavior. Looks like you may want to check the service pack and hot fixes after the service pack 4.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 28, 2011 at 2:24 pm
i configure the maintenance plans to backup all user databses on each instance at different locations. interestingly aaa database eas getting backed up by sql jobs on both instances and at 2 different locations. trust me this issue driving me nuts.. couldn't understand how and why it happend.
February 28, 2011 at 9:34 pm
one way to test this would be, detach one of the db's and see if the other one is still available or not.
February 28, 2011 at 10:20 pm
i detach the db on Instance2 and they were available on Instance1 but i wasn't able to view properties so end up detaching on both instances and attach on one......
trust me guys/gals i am sooooooo confused about this... any SQL gurus have any idea.......
March 3, 2011 at 4:36 pm
That's probably what happened.
There was a backup of DatabaseA as DatabaseA.BKP
Someone did a RESTORE database DatabaseA from disk = 'DatabaseA.BKP'
( without MOVE) on each instance - esp. if instance1 was not up at the time instance2 was restored.... or the RESTORE used a REPLACE ...
As you can prove by doing a
RESTORE FILELISTONLY FROM DISK = 'DatabaseA.BKP",
the physical file location of the .mdf & .ldf is stored within the backup, so an unqualified "RESTORE" will put the files in the same location, if available and possible.
Anyone willing to try and see if you can "achieve" the same even within the same SQL instance, if you change the logical name but do not specify the "MOVE" ?
RESTORE DATABASE DatabaseB from disk = 'DatabaseA.BKP'
[with REPLACE]
should create a new db called DatabaseB that uses the same physical files as DatabaseA ... a superb form of shooting yourself in the foot!
Am not going to try that now, but I think I fell into this trap once with SQL2000 .... Since then I use a template ....
Restore database DatabaseA
from DISK = '\\sql1\sqlbackup\DatabaseA.bak'
with replace,
move 'LogicalFile_Data' to 'D:\MSSQL.1\MSSQL\DATA\Physical_Data.mdf',
move 'LogicalFile_log' to 'E:\MSSQL.1\MSSQL\Log\Physical_log.ldf'
March 6, 2011 at 2:36 pm
Ol'SureHand thank you so much for your reply.... your reply does make sense.. i will definetely want to recreate the scenario.. will try this on my test server.....
March 10, 2011 at 2:10 am
@Ol'SureHand,
I could not reproduce this scenario of 2 live databases using the same set of data and log files. Can you elaborate on the steps you followed please?
March 10, 2011 at 6:14 am
monu.since1984 (3/10/2011)
@Ol'SureHand,I could not reproduce this scenario of 2 live databases using the same set of data and log files. Can you elaborate on the steps you followed please?
You DON'T want to reproduce this scenario, trust me! If you did, you would think you modify DatabaseB, but the changes would certainly affect DatabaseA as well!
Perhaps just to see if it's possible in 2005 and above ... which it might be, at least I think it happened to me long ago while learning the ropes with SQL2000.
You need to use the fully qualified RESTORE command to make sure you know exactly which database goes with which physical file, precisely to avoid complications such as the imaginary scenario I described.
March 10, 2011 at 9:22 pm
@Ol'SureHand,
why i want to try reproducing this is because i believe that 2 live databases can NEVER be able to use the same set of (data and log) files at the same time.
and if it is possible in any any scenario i would like to know the how's and why's. Hence i followed what you described in your post but i am not being able to make 2 dbs use the same files.
I used sql server 2008. If it is only possible in 2000, I dont mind installing an instance of 2000 to test this.
Please let me know in case i've misunderstood anything in this post.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply