Scenario

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Scenario

14 posts, Page 1 of 2

1 2 »»

Scenario

Rate Topic

Display Mode

Topic Options

Author

Message

Ramji29

Posted Thursday, March 25, 2010 11:35 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225, Visits: 419

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

Post #890382

irena.bulatovic

Posted Friday, March 26, 2010 2:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 30, 2011 1:29 AM
Points: 49, Visits: 116

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

Post #890460

Ramji29

Posted Saturday, March 27, 2010 2:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225, Visits: 419

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...... BigGrin

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..

Post #891265

irena.bulatovic

Posted Monday, March 29, 2010 12:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 30, 2011 1:29 AM
Points: 49, Visits: 116

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 BigGrin

. 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

Post #891602

irena.bulatovic

Posted Monday, March 29, 2010 1:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 30, 2011 1:29 AM
Points: 49, Visits: 116

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

Post #891607

vidya_pande

Posted Monday, March 29, 2010 1:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 04, 2012 5:00 PM
Points: 891, Visits: 225

R u not able to change the server settings from trusted mode to mixed mode?

Post #891620

Ramji29

Posted Monday, March 29, 2010 2:33 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225, Visits: 419

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....!!!!

Post #891653

irena.bulatovic

Posted Monday, March 29, 2010 2:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 30, 2011 1:29 AM
Points: 49, Visits: 116

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

Post #891659

Ramji29

Posted Monday, March 29, 2010 3:51 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, August 31, 2012 7:19 AM
Points: 225, Visits: 419

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....

Post #891690

jshailendra

Posted Monday, March 29, 2010 4:32 AM

Old Hand

Group: General Forum Members
Last Login: Friday, December 21, 2012 5:24 AM
Points: 366, Visits: 436

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.

Post #891707

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions