May 11, 2004 at 10:22 am
Hi, I have restored master from Productionon to our development machine loads of times and documented the process.
Now I want to have a new instance on the Development machine so I have an environment to test changes.
I created the instance OK, updated the service pack and got both original Server and new instance working ok together
I also managed after a struggle to restore master from Production on the new instance.
But after doing this I am unable to start both original server and new instance at the same time. If I stop one I can start the other.
Perhaps the problem is that I am not using the right syntax of sp_addserver when drop the incorrect server name. Please could you advise the correct syntax and anything else I should be looking for.
May 11, 2004 at 11:20 am
Best bet:
Where are you restoring the DB files to? If you forgot to alter the destination of the .MDF/.LDF files on restore for the second instance, they will default to the same location as the first instance (I know I am making some assumptions here...). If they end up in the same location, only one DB can have the physical files open at a time.
If you show your restore statements, I could ensure that the syntax is correct.
May 13, 2004 at 2:54 am
This is what I used to restore Master on the new instance. Hope you can advise where I have gone wrong.
USE MASTER
GO
RESTORE DATABASE MASTER
FROM DISK = '\\ihqs60d1\salesdata$\knowledgebase\Backup\master\master_db_200311140100.BAK'
GO
May 13, 2004 at 7:35 am
I believe brendthess is speaking of the production databases. If you do not change the physical location of the databases they will be looking for the old location. Both servers cannot access one set of .mdf / .ldf files at once therefore you cannot start both at the same time. It might help to change the devdb.mdf / .ldf names also just to help keep it all straight.
cortec
May 13, 2004 at 4:51 pm
Your code does not include the MOVE parameter. Thus, by default, the .MDF and .LDF files are placed in the same physical location for both DBs, because RESTORE extracts that information from the backup file.
You have two options, both using the parameter: Change the destination directory, or change the destination filename. Here is a modified version of your command that will do this by moving the files to the location D:\NewInstance\Data\ :
USE MASTER
GO
RESTORE DATABASE MASTER
FROM DISK = '\\ihqs60d1\salesdata$\knowledgebase\Backup\master\master_db_200311140100.BAK'
WITH Recovery, Replace,
MOVE 'Master' TO 'D:\NewInstance\Data\Master.MDF',
MOVE 'MastLog' TO 'D:\NewInstance\Data\MastLog.LDF'
GO
May 14, 2004 at 10:17 am
Brendthess, thanks, that must be what I am looking for.
Presumably I need to use something similar when I restore msdb, model and and the user databases.
I have removed the instance 'usertesting' and then reinstalled it and reapplied service pack 3a (for the umteenth time). I installed the program files and data files in a different directory from the default instance.
Both default instance and new instance are working fine so now I am going to restore master and the other databases based on your email.
Just one question before doing so. I am trying to start the new instance in single mode so that I can restore Master but I get the error message below. I think I solved this on the previous installations by stopping the default instance.
I am wondering if it is also a problem that both instances share the same error log and what do I need to stop this?
Also what is the correct order for restoring? Master, MSDB, Model, User databases or???
The error I am getting:
C:\Program Files\Microsoft SQL Server\usertesting\MSSQL$USERTESTING\Binn>sqlservr.exe -c -m -susertesting
2004-05-14 16:50:11.31 server initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cannot access the file because
it is being used by another process.).
Thanks for your advice
Regards
Stefan
May 18, 2004 at 8:29 am
Hoping for an answer to my earlier email?!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply