March 25, 2010 at 11:35 pm
Friends I've once scenario to discuss....
Lest suppose we have one live server in one domain.
Now I've to make a replica of this server in another domain or some virtual server which not part of this domain.
I've started with restoring all user databases after that once all the user databases are restored I restored the master database and after that I wasn't able to login to this instance as all the login which were there in master belong to different instance & domain and even when I had the sa p/w for the original instance it gave me error as login failed for sa it's not associated with trusted connection.
So now I had two option either rebuild master and change the authentication in mixed mode and then restored master once again or I need to edit the registry to work this instance in mixed mode.....
Now what I was thinking was lets suppose my original instance is running on windows mode and sa is disabled, does this mean I'll not able able to login once this master is restored.
And built in admin are already disabled on both the instances...
Any thoughts on this...
Rohit
March 26, 2010 at 2:44 am
Do you have backup of original master database? If you have restore that database and try to log in with some account from that domain. This is the first time i have heard for someone restoring master from one server to another. We moved our database to several different servers till now but we just restored msdb for jobs and user databases. For transferring logins from one server to another you have scripts or DTS
March 27, 2010 at 2:25 am
I don't need that backup as this was a fresh install and then I started to rebuild the server...
irena.bulatovic (3/26/2010)
We moved our database to several different servers till now but we just restored msdb for jobs and user databases.
I'm don't think that restoring msdb will give me all user databases......:-D
I told u this is something we need to test and for that we need to make a replica of live server on a virtual machine which is not a part of our domain and is a part of different domain.
Hope its more clear now..
March 29, 2010 at 12:57 am
I meant that we restored msdb to transfer our jobs from one server to another and we restored each user database to new server or detached user databases from old server and attached to new server. Of course you can not get user databases just with restoring msdb:-D. But master database have different tables with data that are related to that specific server and when you restore master from some other server you will lose data that are related to that server and get data for some other server . That can not work fine i am sure of that even if i haven't tried that jet
March 29, 2010 at 1:08 am
i read your topic once again and i am not sure what do you want. You can restore or detach attach user database, restore msdb and if you want to have the same logins on new server then you do login transfer but not for sa, or builtin administrators. You can transfer all other logins sql or windows so that they have same sid and passwords like on original server and they will authomatically have the same rights on database object and if you need script for that i can post it here. This way you can have sa from new server and other logins from another server
March 29, 2010 at 1:28 am
R u not able to change the server settings from trusted mode to mixed mode?
March 29, 2010 at 2:33 am
Guys....... I'm not sure whether I was able to explain you as what exactly we're trying to do..... Lets start it all again....
In our comp we're not having any DR plan for this live server except restoring everything to a standby server in case of any mishap.
Secondly we need the master of original database as we have lots of stored proces created in master (Although I know they shouldn't be there) mostly for backups, maintenance & other activities.
And this exercise is to test our current DR plan and come up with the estimated time to make a standby server up.
I'm just seeking some inputs from my fellow forum members as in this case do I have any other options except what I did????
I hope I'm more clear now.....!!!!
Cheers....!!!!
March 29, 2010 at 2:50 am
If you have functions or stored procedures in master you can script them and then create on new server. Master is system database and i doubt that you can successfully restore it from one server to another
March 29, 2010 at 3:51 am
irena.bulatovic (3/29/2010)
If you have functions or stored procedures in master you can script them and then create on new server.
This I haven't tried as I'm not sure except logins and these objects is there anything which might affect the functioning of my new instance. Just like the original one.
irena.bulatovic (3/29/2010)
Master is system database and i doubt that you can successfully restore it from one server to another
Yeah it is possible and I've done it myself....
March 29, 2010 at 4:32 am
Simple rules:
1) Never create any objects in system databases(specially in master)
2) Dont try to recover master from one instance/server to another...script logins from source and run the script on target
Hope this clears.
March 29, 2010 at 9:01 am
jshailendra (3/29/2010)
1) Never create any objects in system databases(specially in master)
I agree but these thing are in place from last 5-7 years and it will be really tough for me to change the process which they are following from so long and that too without any issues.
jshailendra (3/29/2010)
2) Dont try to recover master from one instance/server to another...script logins from source and run the script on target
I agree this too but what if the secondary server on which sql server has been freshly installed just to recover the original server??? still this be a problem???
Cos I already did this test as a part of our yearly DR process where we create a domain with the same name and a scondary server with the same and then restored everything and it was absolutly perfect, now I tried it again in different domain where all the domain users are different, on a different virtual machine and finding are in my original post.
Normally in this sort of DR process like u suggested where I need to script my logins.... how frequently I need to update my script as in case of mishap if my complete server went down and at the time of rebuilding the secondary server how I can get the latest login script, given that my primary server is down and I cannot access it any more.
how to deal with this????
March 30, 2010 at 1:06 am
With restoring master database you restored logins from different domain and lost all logins from domain server is in so you can not log in this way. Only solution is to go back to mixed mode and then backup master and restore it to another server so you will have at least sa to login with.
But as i understand this is some DR solution so if your primary server fails your clients will be transferred to this server but because it is in totaly different domain how will they log in? The only solution is to create trust between this domains so users from one domain can log in to machines from another. I did this but few years ago because my firm bought another firm and for start we created domain trust so all users can work on the same servers.
For login transfer you can create job that will add new logins on the and of every working day
April 8, 2010 at 1:09 pm
yes exactly...you shouldnt be making modifications to the Master database.
1) DTS has Transfer Logins & Transfer Stored Procs tasks. Look into that.
2) you could copy the .MDF and .LDF files over to the new server(if that is an option) and attach it and use Replication/Mirroring/Log Shipping to keep the other database synced with the Live one.
3) Check out this MS Article here: http://support.microsoft.com/kb/246133
April 8, 2010 at 1:15 pm
Also, whenever you restore databases, you typically run into "Orphaned Users" (mismatch between
Logins and Users)
EXEC sp_change_users_login 'update_one','login','user' to fix that.
http://www.akadia.com/services/sqlsrv_logins_and_users.html%5B/url%5D has some info on it
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply