Post reply

Restoring Master and System database : Disaster recovery

  • July 18, 2005 at 2:03 pm

    #110338

    I am looking for any one that want to "chat",email, discuss this subject.

    Short story on problem. Rela

    Server A is restore do Server B. They don't have the same drive configuration. Installed same SQL Server on server B, patched, ect.

    1) Restore master : complete

    2) Start sql instance (named) : failed. The model database is not in the same location as the old one. PLus it is not the same because the master was restored.

    With certain combo of parameters, you also get errors that the user databases are not available and can not be found. Of course they cant' be found, I did not get to resotre them?

    Am I doing this backwords? Resotre userdatabases then ....????

    I have played wit -c -f -m -e, all parameters. Very frustated.

    Thanks,

    Joseph

  • July 19, 2005 at 4:04 am

    #575426

    Hi,

    after restoring master start SQL-Server with -f,

    then restore msdb amd model with move (or set the correct paths in EM).

    Next restart SQL Server, ignore errors about the user databases and then restore the user db's.

    regards

    karl

    Best regards
    karl

  • July 19, 2005 at 6:52 am

    #575451

    Thanks.  Its one of those 'it should work this way'.

    No matter the parameters I use (tried I think all combinations), I basically get this

    2005-07-19 08:32:49.15 spid5     udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device e:\Program Files\Microsoft SQL Server\MSSQL$SRVMAIN021_TEST\data\model.mdf.

    2005-07-19 08:32:49.40 server    SQL server listening on TCP, Shared Memory, Named Pipes.

    2005-07-19 08:32:49.49 server    SQL Server is ready for client connections

    2005-07-19 08:32:49.55 spid5     FCB:pen failed: Could not open device e:\Program Files\Microsoft SQL Server\MSSQL$SRVMAIN021_TEST\data\model.mdf for virtual device number (VDN) 1.

    2005-07-19 08:32:49.66 spid5     Device activation error. The physical file name 'e:\Program Files\Microsoft SQL Server\MSSQL$SRVMAIN021_TEST\data\model.mdf' may be incorrect.

    2005-07-19 08:32:49.99 spid5     Device activation error. The physical file name 'e:\Program Files\Microsoft SQL Server\MSSQL$SRVMAIN021_TEST\data\modellog.ldf' may be incorrect.

    2005-07-19 08:32:50.10 spid5     Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

     

    I know about the parameter for the master database and errorlogs. 

     

    As far as I know, I can't issue an osql command to change the path even since I can not get the instance (service) up and running.

     

    Thanks,

    joseph

  • July 19, 2005 at 6:55 am

    #575452

    Sorry.  Forgot to put this in:

    The output from obove came from running the following statement from the appropriate binn directory.

    d:\..\MSSQL$LATIN_CP850_CIAS\Binn>sqlservr -slatin_cp850_cias -f

     

    Joseph

  • July 19, 2005 at 6:57 am

    #575454

    if you start the server with option -f it should be accessible via EM, even if it complains about model or madb.

    you can then change the paths in master.dbo.sysfiles and master.dbo.sysaltfiles

    karl

    Best regards
    karl

  • July 19, 2005 at 7:20 am

    #575465

    There is nothing running.  If I go to EM and connect I get

     

    "connection can not be established..."

    "Reason: SQL Sever doesn ot exist or access denied..."

     

    I will try starting up with SA.

    Joseph

  • July 19, 2005 at 7:30 am

    #575471

    sorry, i forgot the option -m...

    try

    d:\..\MSSQL$LATIN_CP850_CIAS\Binn>sqlservr -slatin_cp850_cias -f -m

    karl

    Best regards
    karl

  • July 19, 2005 at 7:49 am

    #575481

    thanks, did it.  same thing.

    I really think it has something to do with moving from server A to server B and the drive config is different.  I need to make this work for disaster recovery scenerio.  Have you done this type of thing?

    thanks,

    Joseph

  • July 19, 2005 at 8:16 am

    #575496

    ok, found something on the net:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071#XSLTH3188121122120121120120

    trace flag T3608

    try

    d:\..\MSSQL$LATIN_CP850_CIAS\Binn>sqlservr -slatin_cp850_cias -f -T3608

    for desaster recovery i recommend using exactly the same layout like on the production server, because if desaster strikes you've got a lot of stress and the situation is error prone per se...

    karl

    Best regards
    karl

  • July 19, 2005 at 8:38 am

    #575504

    I agree with you on that (stress).  Problem is management does not consider the potential of DR is enough to pay for the hardware needed to test that way.  We run a shark and tape robotic system for backups.  DR is indivudal servers, mostly peice milled together to get testing done.  My company is regulated and required to test all system at least once a year.

    On top of that, the DR plan is to buy the 100-110 servers (minus 5 that have dr mirrors), build them as fast as possible and get them handed over to us (dba/apps/ect).  We wont get to specify d:\, e:\, g:\...  The server tech guys have a list of the servers and how much hard drive space they need.  Also, the paths will be different on install because of the server names are not going to be the same.  Idea is to limp along as best as possible and then rebuild the datacenter and put back what is need, how it is needed.

    I will check it out.  Thanks for the link.  I will post my results.

    Above got me thinking.  I can create the path (drive letter being the same, maybe), and put the model database where it is expecting it,  hmmm.

    Thanks again,

    Joseph

  • July 19, 2005 at 9:08 am

    #575515

    well, for me, that kind of plan stinks! SQL server is not some puny access db, it needs some thinking to do things right...

    as long as the databases are not on the system drive you could allways change the drive letter in windisk or device manager (as long as the total number of drives remains the same.

    karl

    Best regards
    karl

  • July 22, 2005 at 5:37 am

    #576367

    Thank you for your help. I will post my solution shortly.

    There is an undocumentd parameter -Q that I had to use. With out it my restore does not work with it. I even contacted Microsoft Support and the teir1 guy and his super could not find out what it is for.

    The short story is, despite all that is posted, with the varations of hardware and software out there, you have to plow through this yourself for the company you work for. I would say that the general steps and documentation are accurate.

    Thanks for you help, I will try to have the results posted by Monday afternoon if you want to take a lootk.

  • July 22, 2005 at 8:52 am

    #576447

    yes, i'd be very interested.

    you can never know enough, which goes especially for undocumented options 🙂

    regards karl

    Best regards
    karl

  • August 16, 2005 at 11:49 am

    #582578

    Can you post the results of your test...

  • August 16, 2005 at 11:57 am

    #582581

    Thanks for the reminder.  Yes,  I will post my results tomorrow.  Basically from what I found is that the options need to be tweeked based on the hardware in the enviroment.

     

    Joseph

Viewing 15 posts - 1 through 15 (of 18 total)

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