Restoring Master DB to different server/instance

Question

Post reply

Restoring Master DB to different server/instance

Chris Kitchen

Hall of Fame

Points: 3090
More actions
January 9, 2011 at 7:34 am

#149644

Hi,
I'm using SQL Server 2005 EE on Windows 2003 Server EE and I am restoring a database backup of the Master DB to a different SQL Server instance (same OS and SQL Server version) on a separate physical server. I have ran sp_dropserver and sp_addserver SERVERNAME, 'local' after the restore of the Master database on the new server.
My question is, are there any other "localisation" related steps or considerations I should think about?
Thanks in advance.
www.sqlAssociates.co.uk

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

Mind me asking why you perform the restore of master every night ?

Keep in mind, your instance will not be available to others during your restore ops !

There are other ways of transferring logins, sprocs, ...

regular user objects should not reside in master db !

master is _THE_ system db.

It holds all configuration info, login info, ....

have a look at :http://msdn.microsoft.com/en-us/library/ms190190%28v=SQL.90%29.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 2

Hi,

Thanks for you reply, I never stated that we were restoring the master database every night. We are planning on using the system db restore process as a backup for our DR process. It's an "if all else fails" approach.

I just wanted to know if there were any other "localisation" steps required other than sp_dropserver and sp_addserver.

Thanks.

www.sqlAssociates.co.uk

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Hi Chris,

Since you are migrating the master database, check if any Linked Servers needs to be updated. Also check if the Maintenance Plans needs to be updated to reflect the new server name.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 4

Chris Kitchen (1/9/2011)
Hi,
Thanks for you reply, I never stated that we were restoring the master database every night.

My bad Chris. Sorry for that.

We are planning on using the system db restore process as a backup for our DR process. It's an "if all else fails" approach.

Totally makes sense.

It's an exercise every sqladmin should do !

I just wanted to know if there were any other "localisation" steps required other than sp_dropserver and sp_addserver.
Thanks.

If you restored on a server with another name, have a look at

http://msdn.microsoft.com/en-us/library/ms143799%28v=SQL.90%29.aspx

kb Considerations for Rebuilding the master Database

http://msdn.microsoft.com/en-us/library/ms191431%28v=SQL.90%29.aspx

Keep in mind msdb is also an important system database, especially if you use sqlagent jobs, SSB, alerts, ....

You should take windows security into account if you added non default locations for database files, backups, ...

SQLAgent service account / proxy account authorities to local windows locations, ...