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

Question

Post reply

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

steve lamin-373465

SSC Enthusiast

Points: 115
More actions
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

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

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

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

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...

314546 HOW TO: Move Databases Between Computers That Are Running SQL Server http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach http://support.microsoft.com/?id=224071
221465 INF: Using the WITH MOVE Option with the RESTORE Statement http://support.microsoft.com/?id=221465
240872 HOW TO: Resolve Permission Issues When You Move a Database Between http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers http://support.microsoft.com/?id=246133
274463 INF: Understanding and Troubleshooting the Copy Database Wizard in SQL http://support.microsoft.com/?id=274463
168001 PRB: User Logon and/or Permission Errors After Restoring Dump http://support.microsoft.com/?id=168001
264474 PRB: System Database Backups Cannot be Restored on a Different Build of http://support.microsoft.com/?id=264474
321836 HOW TO: Retrieve a Table or Rows from Database or Transaction Log Backups http://support.microsoft.com/?id=321836

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 2

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

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 3

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

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

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.

dharper3 SSCrazy Points: 2361 More actions · Answer 4

dharper3

SSCrazy

Points: 2361

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?

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 5

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

dharper3 SSCrazy Points: 2361 More actions · Answer 6

dharper3

SSCrazy

Points: 2361

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

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 7

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.

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 8

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

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 9

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

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

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

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 10

S Hodkinson

SSCertifiable

Points: 7529

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

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 11

steve lamin-373465

SSC Enthusiast

Points: 115

October 10, 2006 at 5:51 am

#664967

Yes, thats correct and I select force restore

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 12

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

steve lamin-373465 SSC Enthusiast Points: 115 More actions · Answer 13

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 !

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 14

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