Steps to Restore Master database from an existing bakup in clusteredSQL Server 2005 ?

Question

Steps to Restore Master database from an existing bakup in clusteredSQL Server 2005 ?

Mani-584606

SSChampion

Points: 11788
More actions
November 24, 2008 at 12:52 pm

#196338

Hi,
Could you plz summarize the steps to follow to restore master database from an existing backup file in clustered sql server 2005 as well non clustered Sql server 2005
Thanks in advance
Mani

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not sure about on a clustered server, but Books Online I think covers the subject on a nonclustered server. Check there and see if it helps.

Mani-584606 SSChampion Points: 11788 More actions · Answer 2

Thanks Lynn,

I have followed the below steps:

From the command prompt

1.net stop MSSQL$ins

2.cd C:\Program Files (x86)\Microsoft SQL Server\MSSQL$ins\Binn

3.sqlservr.exe -m -s ins

here I got the below message:

2008-11-24 14:01:48.96 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)

Aug 6 2000 00:57:48

Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2008-11-24 14:01:48.97 server Server Process ID is 6368.

2008-11-24 14:01:48.97 server Logging SQL Server messages in file 'd:\MSSQL$Ins\log\ERRORLOG'.

2008-11-24 14:01:48.97 server SQL Server is starting at priority class 'normal'(8 CPUs detected).

2008-11-24 14:01:49.08 server SQL Server configured for thread mode processing.

2008-11-24 14:01:49.08 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.

2008-11-24 14:01:49.22 server Attempting to initialize Distributed Transaction Coordinator.

2008-11-24 14:01:51.75 spid3 Warning ******************

2008-11-24 14:01:51.75 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.

2008-11-24 14:01:51.75 spid3 Starting up database 'master'.

2008-11-24 14:01:51.89 server Using 'SSNETLIB.DLL' version '8.0.311'.

2008-11-24 14:01:51.89 spid5 Starting up database 'model'.

2008-11-24 14:01:51.91 server SQL server listening on 10.10.10.10: 1234.

2008-11-24 14:01:51.91 server SQL server listening on 10.10.10.10: 1234..

2008-11-24 14:01:51.91 spid3 Server name is 'DBA1\Ins'.

2008-11-24 14:01:51.91 server SQL server listening on 10.10.10.10: 1960.

2008-11-24 14:01:51.91 spid7 Starting up database 'msdb'.

2008-11-24 14:01:51.91 spid8 Starting up database 'ABC_PROD'.

2008-11-24 14:01:51.91 spid9 Starting up database 'DEF_PROD'.

2008-11-24 14:01:51.93 server SQL server listening on 10.10.10.10: 1234

2008-11-24 14:01:51.94 server SQL server listening on TCP, Shared Memory, Named Pipes.

2008-11-24 14:01:51.94 server SQL Server is ready for client connections

2008-11-24 14:01:51.97 spid5 Clearing tempdb database.

2008-11-24 14:01:52.28 spid5 Starting up database 'tempdb'.

2008-11-24 14:01:53.47 spid9 Analysis of database 'DEF_PROD' (6) is 100% complete (approximately 0 more seconds)

2008-11-24 14:01:53.58 spid3 Recovery complete.

4.I tried to connect enterprise manager, the SQL Server is not starting up.

So where Iam going wrong?

plz help me.

Thanks again

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

What is the eror message tht u get when u try to connect from EM? Try thru query analyzer too..

I think u r trying to do the restoration of master database?

MJ

Mani-584606 SSChampion Points: 11788 More actions · Answer 4

Yes Manu, Iam restoring the Master databse from the current backup

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Try connecting thru query analyzer and post the error message.

MJ

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

Based on that log, SQL is started.

Are you connecting to one of the nodes or the virtual instance?

Mani-584606 SSChampion Points: 11788 More actions · Answer 7

Thanks Manu...

The steps:

1.net stop MSSQL$ins

2.cd C:\Program Files (x86)\Microsoft SQL Server\MSSQL$ins\Binn

3.sqlservr.exe -m -s ins( Normally How much time this step will take?)

4.Open Enterprise Manager

5.Query analyzer. Here Iam getting the following error:

[Microsoft][ODBC Sql Server Driver][Sql Server]login failed for user ABC\admin.Reason Server is in single user mode.only one administrator can be connect at this time.

The connection to the sql server has been broken

do you wish to connect now?

yes no cancel.

Thanks

Mani-584606 SSChampion Points: 11788 More actions · Answer 8

Hi Steve,

Actually it is Active\Active clustered Sql server 2005 Environment. But in that I installed one Sql server 2000 instance as a normal installation(not clustered). In that instance Iam trying to restore Master database from an existing backup.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

Mani,

Try this. Connect to ur sql server in normal mode(no single user mode) and open a query analyzer window and stay in it. With this window open do the rest of the steps as listed by you in ir post again and once sql is started as per log u do the required restoration stuff from the query analyzer window(kept open b4 u stopped sql server).

MJ