Restoring System Databases To Alternate Paths

[ErrolDC2]

I'm doing some test restores of my primary sql 2000 box onto a test server that does not share the same drive letters and directory structure. Restoring the non-system databases goes fine without a hitch. However when I restore the master database, SQL now looks for the non-system databases in their original location.

2005-09-20 11:01:20.54 server    Microsoft SQL Server  2000 - 8.00.818 (Intel X86) 
	May 31 2003 16:08:15 
	Copyright (c) 1988-2003 Microsoft Corporation
	Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
 
2005-09-20 11:01:20.54 server    Copyright (C) 1988-2002 Microsoft Corporation. 
2005-09-20 11:01:20.54 server    All rights reserved. 
2005-09-20 11:01:20.54 server    Server Process ID is 3500. 
2005-09-20 11:01:20.54 server    Logging SQL Server messages in file 'e:\MSSQL\log\ERRORLOG'. 
2005-09-20 11:01:20.54 server    SQL Server is starting at priority class 'high'(2 CPUs detected). 
2005-09-20 11:01:20.59 server    SQL Server configured for thread mode processing. 
2005-09-20 11:01:20.59 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 
2005-09-20 11:01:20.63 server    Attempting to initialize Distributed Transaction Coordinator. 
2005-09-20 11:01:21.68 spid4     Warning ****************** 
2005-09-20 11:01:21.68 spid4     SQL Server started in single user mode. Updates allowed to system catalogs. 
2005-09-20 11:01:21.68 spid4     Starting up database 'master'. 
2005-09-20 11:01:21.81 server    Using 'SSNETLIB.DLL' version '8.0.818'. 
2005-09-20 11:01:21.81 spid5     Starting up database 'model'. 
2005-09-20 11:01:21.81 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\data\model.mdf. 
2005-09-20 11:01:21.81 spid5     FCB::Open failed: Could not open device E:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf for virtual device number (VDN) 1. 
2005-09-20 11:01:21.81 spid5     Device activation error. The physical file name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf' may be incorrect. 
2005-09-20 11:01:21.81 spid4     Server name is 'DFIINT-FLSVR'. 
2005-09-20 11:01:21.81 spid4     Skipping startup of clean database id 17

and it goes on like that for all the databases. So obviously I did something wrong. Can someone help me understand what that is?

 

[mrdenny]

You have to restore master first. After you restore master it will look in the first servers locations for the databases. The paths to all the mdf files are stored in the master database, so when you restore master it's looking in the wrong place for everything.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief