HOW TO RESTORE MASTER DB IN MSSQL 2005 SP 2

Question

Post reply

HOW TO RESTORE MASTER DB IN MSSQL 2005 SP 2

NEW TO SQL

SSC Veteran

Points: 227
More actions
April 27, 2010 at 7:18 am

#144462

Hello,
please give me the step by step procedure to restore Master DB.
In the heading I've mentioned SP2 so please excuse for that.
please check the scenario carefully. i don't want anyone replying just for fun sake.
------- Scenario -----
1 - Master DB backup was taken on Computer A - (MSSQL 2005 Standard Edition SP3 64 bit)
2 - New MSSQL Server was installed on Computer B - (with exactly the same version)
3 - Both A & B servers have the same version (9.00.4035.00)
4 - Master DB needs to be restored on Computer B along with other System DBs (I'm already done with other system DBs).
Now, looking for some help here. Thanks.

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

pls go through this URL.

http://technet.microsoft.com/en-us/library/ms190679.aspx

Pradeep Singh

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Be aware that you'll need to change the SQL Server name (sp_dropserver, sp_addserver) if this is a different Windows host.

If paths are different for databases, you'll also have errors you need to fix in Master

george sibbald SSC Guru Points: 104210 More actions · Answer 3

This is just for fun but here goes anyway................

I wouldn't do it this way

Unless you have lots of linked servers (which won't decrypt anyway), user error massages, SPs in master, proxies and credentials just script out the contents of master on server A and load to server B. So if you only have logins to copy across look up sp_help_revlogin and use that.

Same for msdb. Maintenance plans won't work, SSIS packages and sqlagent jobs are not guaranteed to work. Recreate maintenance plans from scratch, use save as for SSIS and script out jobs.

model you should be fine with but in most cases it is unaltered so no need to restore across.

just my 2 pence worth.

---------------------------------------------------------------------

NEW TO SQL SSC Veteran Points: 227 More actions · Answer 4

Is it not necessary to move the resource DB as well along with Master? i read in blogs that resource DB should also be restored to the new server... please advise

george sibbald SSC Guru Points: 104210 More actions · Answer 5

The one you already have on server B is the version you want so use that one.

It is vital that the resource db files are in the same directory as master.

---------------------------------------------------------------------

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

The Resource db is for the version of SQL Server, not the instance. You don't need to move it.