Restoring Master database different versions of same service pack

Question

Restoring Master database different versions of same service pack

MarvinTheAndriod

SSCarpal Tunnel

Points: 4972
More actions
September 1, 2010 at 8:41 am

#144945

Restoring a Master Database to Another Server.
If you are restoring the master database to a different server, you have to set SQL Server up with the same version of the service pack. For example, 9.00.3043 and 9.00.3050 are the same service pack but different versions. If you were to try and restore the master from a server that was running with a set up of 9.00.3043, to a new server, you would have had to have kept the version of the service pack, because if you try to download the latest version of this service pack from Microsoft they may well have replaced it with 9.00.3050. How do people deal with this?
One answer is keep the Service packs updated on the production environment to the latest version. But this in a business environment isn’t realistic?
Or do you keep every version of the Service Pack downloaded in a central location ?
Or is there another way I am completely missing?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

I don't keep each patch downloaded, I usually do try to keep my environments the same version. I know it is difficult and outright impossible in some business cases.

But in your case 9.00.3043 is SP2 (re-release) (don't know the hot fix, as the SP2 is 9.00.3042) and other version is 9.00.3050 is SP2+KB933508.

So what have to do is install SQL Server + SP2 (re-release version); restore your database and patch it up to your supported version level 9.00.3050. Before restoring it into new server.

Ref: http://support.microsoft.com/kb/933508/en-us (SQL Server 2005 SP2 Re-Release)

Ref: http://sqlserverbuilds.blogspot.com/

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 2

So what have to do is install SQL Server + SP2 (re-release version); restore your database and patch it up to your supported version level 9.00.3050. Before restoring it into new server.

What if it is a DR scenario and the prime server is unavailable, but you have backups?

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 3

In that case your new server should have been built to same patch level before doing the restore. If you don't have it documented what the patch level was for the backup you can execute following command to figure it out:

RESTORE HEADERONLY FROM FILE = 'C:\Backup.bak'

Look at SoftwareVersionMajor, SoftwareVersionMinor, and SoftwareVersionBuild.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Lowell SSC Guru Points: 323518 More actions · Answer 4

for me, the master database doesn't have anything of interest besides logins;

i would restore a master database as a user db so i could extract logins, if they were not scripted out to the disaster files already, but would not bothere trying to restore it as the core master database.

I have a handful of sp_* type procs in master, but they are really to aid cross database scripting of objects and stuff....and they are scripted to the disaster recovery scripts as well.

I would end up restoring each database as needed on the new server, so i don't see a solid need to ever restore master on a different machine.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 5

That's a good point, I'll have to remember that for future. I script out login name/db relations and db attached on each server for my DR. Never thought about scripting out the full login information, because I always had system db backups (luckily :D).

Thanks for the tip Lowell :).

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

I am with Lowell. I hate restoring master. Asking for issues, especially in DR for me. Easy to recreate logins in the worst case, but I tend to save them out as a script.

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 7

Lowell (9/1/2010)
for me, the master database doesn't have anything of interest besides logins;
i would restore a master database as a user db so i could extract logins, if they were not scripted out to the disaster files already, but would not bothere trying to restore it as the core master database.
I have a handful of sp_* type procs in master, but they are really to aid cross database scripting of objects and stuff....and they are scripted to the disaster recovery scripts as well.
I would end up restoring each database as needed on the new server, so i don't see a solid need to ever restore master on a different machine.

Second this!

I script out all the logins as a daily job for DR 😎

Lowell SSC Guru Points: 323518 More actions · Answer 8

On a similar thread, Gail Shaw (Gilamonster) reminded us that linked servers and which procedures are scheduled to start when SQL starts are in master, so they need to be scripted out too for a complete recovery.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 9

The other problem I have identified is that you can't get the Sa password out using the sp_help_revlogin stored procedure see my other thread.

http://www.sqlservercentral.com/Forums/Topic1002245-146-1.aspx

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 10

You wouldn't want the sa password to be scripted out, even encrypted. That would allow someone the chance to move it to another instance and spend time trying to crack it.