Testing DR process - unable to restore master db
-
first, sorry this is long - I've spent some time trying to figure this out and want to learn something from effort.
My general question is am I attempting to do something that will not work regardless what I do? My specific question is what am I missing while attempting to restore master db. If I am in cmd screen, type sqlcmd and do not get 1> etc, instead I get "sqlcmd error mssql native client communication link failure no process is on the other end of the pipe"
My work task is to document all steps necessary to restore one of our core systems. After documentation of correct steps then use the newly restored system as test environment to test various upgrades we need to do. Our current system is at SQL 2005, with no SP on it.
During SQL server install, I made a mistake on the collation selection but not discovered until after restored msdb, model - installed and restored application db. I did not restore MASTER. Once I discovered problem, I thought I could learn something from trying to fix overall problem by restoring Master.
Read many forums, technet BOL etc. These are steps I took during most recent attempt:
1) Copied correct master backup to c:\bk\master.bak
2) Server is on domain as different server name from production system
3) SQL server service and sql agent service using domain user with administrative rights
4) logged into DR server with that user name
5) Stopped SQL services
6) from cmd screen navigated to c:\program....mssql\binn
7) sqlservr.exe -m to start as single user. Appears to run through all messages and be mostly ok, but no c prompt at the end, just waiting cursor
8) opened SSMS right click on master > restore > type in master to restore > selected backup > select master > options = overwrite db > OK
error Transport-level error has occurred when receiving results form the server (provider; Shared Memory Provider, error:0 - The pipe has been ended) MS error 109).
9) decided to try to restore from sqlcmd prompt, but can't get a c prompt after using it to start sql as single user.
Am I just wasting my time, or am I missing something simple to try and get master restored? thanks in advance for any ideas.
-
Start SQL in single user mode.
Open SSMS and restore using TSQL.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
SSMS - connected using Windows authentication
click New Query
error login faied for user.....domain\username
Server is in single user mode only one administrator can connect at a time. MS error 18461
?
-
That's because you're probably connected in Object Explorer too and when you open the new query window it makes another connection. Disconnect from object explorer and try to make the tsql connection again.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
thanks for ideas, still stuck.
I am unable to connect to server in SSMS, I thought I had connected, but I keep getting the error that server is in single use mode and unable to allow another administrator to connect.
I am attempting to connect using windows authentication, using domain user that has administrative rights. Should I logon another way when I am in single use mode?
-
OK, I did it. It was along the lines of what you were saying, but I had to go through it this way.
AFTER all the stuff about stopping services, putting server in single user mode....
open SSMS
close summary tab
close object explorer
click File > disconnect - (without this step - it still gave error)
click New query
execute commands - it worked
restoring master is tricky.
-
No, restoring master is easy. Getting a sole connecetion to the server can be tricky.
Glad it all worked out.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply