Post reply

Moving Master & System DB''s to new Secondary Server

  • October 9, 2006 at 7:47 am

    #62211

    Hi, Can anyone help me with step by step instructions on how to move the master DB to a new server.

    Both servers are SQL 2000 sp3a and collation type is the same.

    I started by placing the the 2nd server in single user mode and then open up the EM and force restore the master backup from disk to a new location. ie. Original server data files located on the F:\ drive & 2nd servers data files are destined to the d:\ drive

    The Problem is that, after restoring the master db,  when I restart the SQL services I`m unable to establish a connection or restart sql services, as these start and stop abruptly.

    Help please

     

  • October 9, 2006 at 7:56 am

    #664780

    Can you install the secondary server will exactly same disk structure as on primary? If yes, then its piece of cake...

    1. Stop SQL on Secondary and Primary.

    2. Move all files from Primary to seconday at same location.

    3. Start the SQL Sevrer on secondary.

    If your file structure is not same then...

  • October 9, 2006 at 8:08 am

    #664818

    Thx for the Links,

    Unfortunately, the disc structure between Primary and secondary Servicers the Disc stucture and partions aren`t the same.

    Also can`t copy source master data files to destination as primary server is still in use. So , I thought the easiest way would be to take a backup of the primary master Db and restores this in Single User mode on the Secondary server, BUT, as soon as I complete this operation I`m unable to establish a connection to the secondary server and the SQL server services start and stop abruptly ??? Aleternatively, I`ve also tried restoring the Master db with opening EM, and instead used QA to execute the following script.

    Restore database Master

    from Disk = 'E:\MSSQL\Backups\cs-w2ksql1\master backup\master_daily backup.bak'

    with recovery,

    move 'master' to 'D:\MSSQL\Data\master.mdf',

    move 'mastlog' to 'D:\MSSQL\Data\mastlog.mdf'

    Go

    Both EM and QA results in unable to restart services and create a connection to server. Help please

  • October 9, 2006 at 8:39 am

    #664823

    Here's a copy of SQL error log after restoring the master DB>>

    The missing User database errors are expected as I haven`t copied across these yet until I`m able to restore the  master DB. C an Anyone HELP please ??>?>

    2006-10-09 15:29:53.80 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

     Dec 17 2002 14:22:05

     Copyright (c) 1988-2003 Microsoft Corporation

     Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    2006-10-09 15:29:53.80 server    Copyright (C) 1988-2002 Microsoft Corporation.

    2006-10-09 15:29:53.80 server    All rights reserved.

    2006-10-09 15:29:53.80 server    Server Process ID is 3680.

    2006-10-09 15:29:53.80 server    Logging SQL Server messages in file 'd:\MSSQL\log\ERRORLOG'.

    2006-10-09 15:29:53.80 server    SQL Server is starting at priority class 'normal'(8 CPUs detected).

    2006-10-09 15:29:53.91 server    SQL Server configured for thread mode processing.

    2006-10-09 15:29:53.91 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.

    2006-10-09 15:29:54.04 server    Attempting to initialize Distributed Transaction Coordinator.

    2006-10-09 15:29:54.07 server    Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b

    2006-10-09 15:29:54.07 spid3     Starting up database 'master'.

    2006-10-09 15:29:54.08 spid3     1 transactions rolled back in database 'master' (1).

    2006-10-09 15:29:54.08 spid3     Recovery is checkpointing database 'master' (1)

    2006-10-09 15:29:54.10 spid3     Server name is 'CS-W2K3SQL1'.

    2006-10-09 15:29:54.10 server    Using 'SSNETLIB.DLL' version '8.0.766'.

    2006-10-09 15:29:54.10 spid5     Starting up database 'model'.

    2006-10-09 15:29:54.10 spid5     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\MSSQL7\DATA\model.mdf.

    2006-10-09 15:29:54.10 spid5     FCB:pen failed: Could not open device f:\MSSQL7\DATA\model.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid5     Device activation error. The physical file name 'f:\MSSQL7\DATA\model.mdf' may be incorrect.

    2006-10-09 15:29:54.10 spid8     Starting up database 'msdb'.

    2006-10-09 15:29:54.10 spid9     Starting up database 'dream'.

    2006-10-09 15:29:54.10 spid10    Starting up database 'DreamH'.

    2006-10-09 15:29:54.10 spid8     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\MSSQL7\DATA\msdbdata.mdf.

    2006-10-09 15:29:54.10 spid9     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\Dream.mdf.

    2006-10-09 15:29:54.10 spid10    udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\DreamH.mdf.

    2006-10-09 15:29:54.10 spid8     FCB:pen failed: Could not open device f:\MSSQL7\DATA\msdbdata.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid9     FCB:pen failed: Could not open device f:\mssql7\data\Dream.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid8     Device activation error. The physical file name 'f:\MSSQL7\DATA\msdbdata.mdf' may be incorrect.

    2006-10-09 15:29:54.10 spid9     Device activation error. The physical file name 'f:\mssql7\data\Dream.mdf' may be incorrect.

    2006-10-09 15:29:54.10 spid10    FCB:pen failed: Could not open device f:\mssql7\data\DreamH.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid10    Device activation error. The physical file name 'f:\mssql7\data\DreamH.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid11    Starting up database 'BRIT_TEST_Non_Prop'.

    2006-10-09 15:29:54.11 spid11    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\BRIT_TEST_Non_Prop.mdf.

    2006-10-09 15:29:54.11 spid12    Starting up database 'BRIT_LIVE_NON_PROP'.

    2006-10-09 15:29:54.11 spid12    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\brit_live_non_prop_data.MDF.

    2006-10-09 15:29:54.11 spid13    Starting up database 'BRIT_LIVE_PROP'.

    2006-10-09 15:29:54.11 spid11    FCB:pen failed: Could not open device F:\MSSQL7\data\BRIT_TEST_Non_Prop.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid13    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\C33prop.MDF.

    2006-10-09 15:29:54.11 spid12    FCB:pen failed: Could not open device F:\MSSQL7\data\brit_live_non_prop_data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid11    Device activation error. The physical file name 'F:\MSSQL7\data\BRIT_TEST_Non_Prop.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid13    FCB:pen failed: Could not open device F:\MSSQL7\data\C33prop.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid12    Device activation error. The physical file name 'F:\MSSQL7\data\brit_live_non_prop_data.MDF' may be incorrect.

    2006-10-09 15:29:54.11 spid13    Device activation error. The physical file name 'F:\MSSQL7\data\C33prop.MDF' may be incorrect.

    2006-10-09 15:29:54.11 spid13    Starting up database 'BRIT_TEST_PROP'.

    2006-10-09 15:29:54.11 spid10    Starting up database 'Drm_CITH'.

    2006-10-09 15:29:54.11 spid13    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\BRIT_TEST_PROP.mdf.

    2006-10-09 15:29:54.11 spid10    udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\Drm_CITH.mdf.

    2006-10-09 15:29:54.11 spid13    FCB:pen failed: Could not open device F:\MSSQL7\data\BRIT_TEST_PROP.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid8     Starting up database 'PO_TEMP'.

    2006-10-09 15:29:54.11 spid10    FCB:pen failed: Could not open device f:\mssql7\data\Drm_CITH.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid9     Starting up database 'PurchaseOrders'.

    2006-10-09 15:29:54.11 spid8     udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\PO_TEMP.mdf.

    2006-10-09 15:29:54.11 spid13    Device activation error. The physical file name 'F:\MSSQL7\data\BRIT_TEST_PROP.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid9     udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\PurchaseOrders_Data.MDF.

    2006-10-09 15:29:54.11 spid11    Starting up database 'Expenses'.

    2006-10-09 15:29:54.11 spid8     FCB:pen failed: Could not open device F:\MSSQL7\data\PO_TEMP.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid10    Device activation error. The physical file name 'f:\mssql7\data\Drm_CITH.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid11    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\Expenses_Data.MDF.

    2006-10-09 15:29:54.11 spid9     FCB:pen failed: Could not open device F:\MSSQL7\data\PurchaseOrders_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid8     Device activation error. The physical file name 'F:\MSSQL7\data\PO_TEMP.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid11    FCB:pen failed: Could not open device F:\MSSQL7\data\Expenses_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid9     Device activation error. The physical file name 'F:\MSSQL7\data\PurchaseOrders_Data.MDF' may be incorrect.

    2006-10-09 15:29:54.11 spid11    Device activation error. The physical file name 'F:\MSSQL7\data\Expenses_Data.MDF' may be incorrect.

    2006-10-09 15:29:54.11 spid13    Starting up database 'PayBase6_0'.

    2006-10-09 15:29:54.11 spid13    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\Data\pb60.mdf.

    2006-10-09 15:29:54.11 spid13    FCB:pen failed: Could not open device F:\MSSQL7\Data\pb60.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid13    Device activation error. The physical file name 'F:\MSSQL7\Data\pb60.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid11    Starting up database 'CS-W2KXP_Farm'.

    2006-10-09 15:29:54.11 spid11    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\CS-W2KXP Farm_Data.MDF.

    2006-10-09 15:29:54.11 spid11    FCB:pen failed: Could not open device F:\MSSQL7\data\CS-W2KXP Farm_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid10    Starting up database 'CS-W2KMSAM1_Repository'.

    2006-10-09 15:29:54.11 spid11    Device activation error. The physical file name 'F:\MSSQL7\data\CS-W2KXP Farm_Data.MDF' may be incorrect.

    2006-10-09 15:29:54.11 spid10    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\CS-W2KMSAM1_Repository.mdf.

    2006-10-09 15:29:54.11 spid8     Starting up database 'CS-W2KMSAM2_Repository'.

    2006-10-09 15:29:54.11 spid8     udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\CS-W2KMSAM2_Repository.mdf.

    2006-10-09 15:29:54.11 spid10    FCB:pen failed: Could not open device F:\MSSQL7\data\CS-W2KMSAM1_Repository.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid12    Starting up database 'Dream_T'.

    2006-10-09 15:29:54.11 spid12    udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\Dream_T.mdf.

    2006-10-09 15:29:54.11 spid10    Device activation error. The physical file name 'F:\MSSQL7\data\CS-W2KMSAM1_Repository.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid8     FCB:pen failed: Could not open device F:\MSSQL7\data\CS-W2KMSAM2_Repository.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid5     Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    2006-10-09 15:29:54.11 spid12    FCB:pen failed: Could not open device f:\mssql7\data\Dream_T.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid8     Device activation error. The physical file name 'F:\MSSQL7\data\CS-W2KMSAM2_Repository.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid12    Device activation error. The physical file name 'f:\mssql7\data\Dream_T.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid9     Starting up database 'iwss'.

    2006-10-09 15:29:54.11 spid9     udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\iwss.mdf.

    2006-10-09 15:29:54.11 spid9     FCB:pen failed: Could not open device F:\MSSQL7\data\iwss.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid10    Starting up database 'Dream_TH'.

    2006-10-09 15:29:54.11 spid10    udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\Dream_TH.mdf.

    2006-10-09 15:29:54.11 spid9     Device activation error. The physical file name 'F:\MSSQL7\data\iwss.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid11    Starting up database 'Codeman_test'.

    2006-10-09 15:29:54.11 spid10    FCB:pen failed: Could not open device f:\mssql7\data\Dream_TH.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid11    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\Data\Codeman_test_Data.MDF.

    2006-10-09 15:29:54.11 spid13    Starting up database 'Codeman_baseline_6y'.

    2006-10-09 15:29:54.11 spid10    Device activation error. The physical file name 'f:\mssql7\data\Dream_TH.mdf' may be incorrect.

    2006-10-09 15:29:54.11 spid11    FCB:pen failed: Could not open device F:\MSSQL7\Data\Codeman_test_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid13    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\Data\Codeman_baseline_6y_Log.ldf.

    2006-10-09 15:29:54.11 spid13    FCB:pen failed: Could not open device F:\MSSQL7\Data\Codeman_baseline_6y_Log.ldf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.11 spid11    Device activation error. The physical file name 'F:\MSSQL7\Data\Codeman_test_Data.MDF' may be incorrect.

    2006-10-09 15:29:54.11 spid13    Device activation error. The physical file name 'F:\MSSQL7\Data\Codeman_baseline_6y_Log.ldf' may be incorrect.

    2006-10-09 15:29:54.11 spid12    Starting up database 'CPS_Farm'.

    2006-10-09 15:29:54.11 spid12    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\CPS_Farm_Data.MDF.

    2006-10-09 15:29:54.11 spid12    FCB:pen failed: Could not open device F:\MSSQL7\data\CPS_Farm_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.13 spid12    Device activation error. The physical file name 'F:\MSSQL7\data\CPS_Farm_Data.MDF' may be incorrect.

    2006-10-09 15:29:54.13 spid13    Starting up database 'DQ'.

    2006-10-09 15:29:54.13 spid13    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\Data\DQ_Data.MDF.

    2006-10-09 15:29:54.13 spid8     Starting up database 'Drm_CIT'.

    2006-10-09 15:29:54.13 spid13    FCB:pen failed: Could not open device F:\MSSQL7\Data\DQ_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.13 spid8     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\Drm_CIT.mdf.

    2006-10-09 15:29:54.13 spid13    Device activation error. The physical file name 'F:\MSSQL7\Data\DQ_Data.MDF' may be incorrect.

    2006-10-09 15:29:54.13 spid8     FCB:pen failed: Could not open device f:\mssql7\data\Drm_CIT.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.13 spid11    Starting up database 'CitrixAdvancedAccessControl'.

    2006-10-09 15:29:54.13 spid11    udopen: Operating system error 3(error not found) during the creation/opening of physical device F:\MSSQL7\data\CitrixAdvancedAccessControl_Data.MDF.

    2006-10-09 15:29:54.13 spid8     Device activation error. The physical file name 'f:\mssql7\data\Drm_CIT.mdf' may be incorrect.

    2006-10-09 15:29:54.13 spid11    FCB:pen failed: Could not open device F:\MSSQL7\data\CitrixAdvancedAccessControl_Data.MDF for virtual device number (VDN) 1.

    2006-10-09 15:29:54.13 spid11    Device activation error. The physical file name 'F:\MSSQL7\data\CitrixAdvancedAccessControl_Data.MDF' may be incorrect.

  • October 9, 2006 at 10:41 pm

    #664911

    Would seem as though msdb and model databases do not exist or at least not where master thinks they should be.  Have you restored these two databases after master has been restored?

     

  • October 10, 2006 at 2:36 am

    #664938

    Hi dharpr3,

    You are correct I did not restore the msdb and model databses from primary to secondary server, I assumed that i would be able to complete this after restoring the master database. I will give it a go, with, restoring the master, model, and msdb from primary to secondary server, and get back to with the results.

    Hi, I`ve just tried to restore the model db and was prompted that I`m not able to complete this in single user mode, I assume that I need not bother try restoring the msdb db as i will be prompted that i cant restore this db as well, while in single user mode.

    The Issue is, after i restore the master in Single user mode, the SQL service attempt to restart after i`ve completed this task, but the SQL services immediately shutdown, which results in no connectivity to the server either thru EM or QA and I`m unable to proceed with any further restores of system or user Db's. Please see SQL Error log above. I begining to suspect that it maybe a permissions problem, as the primary server is located with a different domain from which the secondary server is located in. A 2way trust does exist between the domain, and I`ve included the secondary domain admins global group account and secondary domain sql service account with the primary servers Sys admins role. Or am I reaching out too far here. I know that the restore shouldn`t be this difficult and cant understand why I`m experiencing so much trouble here.... !!

    Any and all help would be greatly appreciated, Many Thanks. Steve

  • October 10, 2006 at 3:57 am

    #664946

    Restore master to secondary with move. 

     This needs to be done by setting -m flag in startup parameters (via em under general). restore can then be performed via sql em or QA.

    It will stop sql server, you then need to restart, remove parameter  and then restore msdb and model with move

  • October 10, 2006 at 4:39 am

    #664954

    Hi, I restored master to secondary, while in single user mode. Restarted the Sql services and then lost connectivity to the server, as the sqlservices immediately shutdown. Sql error log is as before.

    I seem to be going around in circles here.

    So now my plan is to reinstall sql, and service pack it (sp3a), restore user Db's and then resolve Logins.

    Any other suggestions would be appreciated. 

  • October 10, 2006 at 5:02 am

    #664957

    As I remember, the order to move the system databases is model, msdb then master.  Microsoft have got quite a bit on their site about this.

    The other thing I've tried, which falls under the category of 'weird but true'.............................. try zipping the backups of the databases on their original server and unzipping them in their new location.  You can then use them to restore into the model, msdb and master.

     

    Madame Artois

  • October 10, 2006 at 5:17 am

    #664959

    Hi, I dont think that order to oder of restoration is correct as the primary server data files are located on the C:\ drive and the Destination Data file are too be located on the the Secondary servers D:\ Drive.

    Surely, if i restore, as you suggest, restore model, msdb to secondary srvr  and then restore master to new path location because the master is orginally from primary sever the sys tables will incorrectly reference a file path for the model and msdb db's to a file location that doesn`t exist.

    Please see error log below.

    2006-10-09 15:29:53.80 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

     Dec 17 2002 14:22:05

     Copyright (c) 1988-2003 Microsoft Corporation

     Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    2006-10-09 15:29:53.80 server    Copyright (C) 1988-2002 Microsoft Corporation.

    2006-10-09 15:29:53.80 server    All rights reserved.

    2006-10-09 15:29:53.80 server    Server Process ID is 3680.

    2006-10-09 15:29:53.80 server    Logging SQL Server messages in file 'd:\MSSQL\log\ERRORLOG'.

    2006-10-09 15:29:53.80 server    SQL Server is starting at priority class 'normal'(8 CPUs detected).

    2006-10-09 15:29:53.91 server    SQL Server configured for thread mode processing.

    2006-10-09 15:29:53.91 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.

    2006-10-09 15:29:54.04 server    Attempting to initialize Distributed Transaction Coordinator.

    2006-10-09 15:29:54.07 server    Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b

    2006-10-09 15:29:54.07 spid3     Starting up database 'master'.

    2006-10-09 15:29:54.08 spid3     1 transactions rolled back in database 'master' (1).

    2006-10-09 15:29:54.08 spid3     Recovery is checkpointing database 'master' (1)

    2006-10-09 15:29:54.10 spid3     Server name is 'CS-W2K3SQL1'.

    2006-10-09 15:29:54.10 server    Using 'SSNETLIB.DLL' version '8.0.766'.

    2006-10-09 15:29:54.10 spid5     Starting up database 'model'.

    2006-10-09 15:29:54.10 spid5     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\MSSQL7\DATA\model.mdf.

    2006-10-09 15:29:54.10 spid5     FCB:pen failed: Could not open device f:\MSSQL7\DATA\model.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid5     Device activation error. The physical file name 'f:\MSSQL7\DATA\model.mdf' may be incorrect.

    2006-10-09 15:29:54.10 spid8     Starting up database 'msdb'.

    2006-10-09 15:29:54.10 spid9     Starting up database 'dream'.

    2006-10-09 15:29:54.10 spid10    Starting up database 'DreamH'.

    2006-10-09 15:29:54.10 spid8     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\MSSQL7\DATA\msdbdata.mdf.

    2006-10-09 15:29:54.10 spid9     udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\Dream.mdf.

    2006-10-09 15:29:54.10 spid10    udopen: Operating system error 3(error not found) during the creation/opening of physical device f:\mssql7\data\DreamH.mdf.

    2006-10-09 15:29:54.10 spid8     FCB:pen failed: Could not open device f:\MSSQL7\DATA\msdbdata.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid9     FCB:pen failed: Could not open device f:\mssql7\data\Dream.mdf for virtual device number (VDN) 1.

    2006-10-09 15:29:54.10 spid8     Device activation error. The physical file name 'f:\MSSQL7\DATA\msdbdata.mdf' may be incorrect.

    Any help in resolving this would be great, thanks for all suggestions so far, but this is proving to be somewhat problamatic. Thanks again.

    Oh, the destination for the physical file name should be D:\MSSQL\Data\xxx.mdf

  • October 10, 2006 at 5:47 am

    #664966

    Are you changing the path of the database when you restore? That is, having chosen Restore database, selecting the Options tab, double click in Restore As and changing the path to D:\MSSQL\Data\XXX?

    Madame Artois

  • October 10, 2006 at 5:51 am

    #664967

    Yes, thats correct and I select force restore

  • October 10, 2006 at 6:16 am

    #664977

     Is there an F: \drive on the new server?  As the SQL Server and  the master, model and msdb files loaded on the F:\ drive of the original server, is it possible to make an F:\ drive on the new server and install SQL Server there?  Then the master, model and msdb can be restored there. The production databases can sit on the D:\ drive.

    Madame Artois

  • October 10, 2006 at 7:09 am

    #664991

    Hi,

    Unfortuneately their will be no F:\ drive on the new server. The only drives available on the Second server(destinaton)are:

    C:\ [system] program files(x86\microsoft sql server\mssql\binn

    D:\[data]MSSQL\data

    E:\[Logs]MSSQL\Data_Logs

    the primary server includes C:\, D:\, E:\ F:\ G:\ I:\ (a complete mess I know) but I`ve been tasked with consoilidating this on to the new server. If I had a F;\ drive I would have used this.

    So, i`m wondering, if i start sql with sqlsrvr -m -c -T3608 after restaring the master, which doesn`t load any db's except the master, could i then restare the model and msdb db's in single user mode whith this flag ??? Would you know if it is possible to restore the model & msdb with sql in this state??

    Thx for your help so far, you`re a STAR !

     

     

  • October 10, 2006 at 7:55 am

    #665003

    Where do the SQL folders repldata,ftdata,jobs,log,data and backup sit?  Are they on C:\ or D:\?  Some bits of SQL Server are very specific as to where they reside e.g. device backups and I think this is why your restores are arguing with the new server.

    Are you backing the databases up to devices rather than files?  Often these are easier to move as you can copy the devices from one server to another and then restore (changing the physical location).

    Was no one logged into the server when the backups were taken?  Do the logins have Windows or mixed authenications?  The logins will be specific to the server as well as the databases.   Sometimes you have to run sp_validatelogins (on the databases) to disassociate logins from the database.

    Madame Artois

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

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