SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recovery a SQL 2000 server on an alternate server (i.e. after a major server failure).


Recovery a SQL 2000 server on an alternate server (i.e. after a major server failure).

Author
Message
wsullivan66
wsullivan66
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 19
Hi, I'm currently in the process of testing some DR scenario's and have found that i'm unable to recover system DB's on an alternate server.

Overview - we currently backup the Master, Model, MSDB and all user databases

Steps taken
1. Install O/S (windows 2003)
Default O/S Locale same as on the failed server
Install to same service pack level as failed server
2. Install SQL 2000 (as on the failed server)
Changed default installation path to E:\ (Application and Data paths)
Used Local system account for both server and agent
Mixed mode & Windows authentication
Per processor license
SQL Regional settings same as on failed server
Installed service pack 4
3. Stopped SQL server from running
4. Put SQL instance in single user mode
E:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\Binn>sqlservr.exe -c -m -f -s EASYSHIP
Restore the master db
5.

SQL server will not start so I’m unable to restore MODEL or MSDB.
See error log below

2008-08-06 12:26:48.67 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2008-08-06 12:26:48.70 server Copyright (C) 1988-2002 Microsoft Corporation.
2008-08-06 12:26:48.70 server All rights reserved.
2008-08-06 12:26:48.70 server Server Process ID is 1448.
2008-08-06 12:26:48.70 server Logging SQL Server messages in file 'e:\mssql7\MSSQL$EASYSHIP\log\ERRORLOG'.
2008-08-06 12:26:48.74 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2008-08-06 12:26:49.03 server SQL Server configured for thread mode processing.
2008-08-06 12:26:49.07 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2008-08-06 12:26:54.38 server Attempting to initialize Distributed Transaction Coordinator.
2008-08-06 12:26:56.71 spid3 Starting up database 'master'.
2008-08-06 12:26:57.13 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2008-08-06 12:26:57.15 spid5 Starting up database 'model'.
2008-08-06 12:26:57.18 server SQL server listening on 10.168.1.100: 1234.
2008-08-06 12:26:57.18 server SQL server listening on 127.0.0.1: 1234.
2008-08-06 12:26:57.16 spid5 udopen: Operating system error 3(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\model.mdf.
2008-08-06 12:26:57.18 spid5 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\model.mdf for virtual device number (VDN) 1.
2008-08-06 12:26:57.18 spid3 Server name is 'MKSWVIR01\EASYSHIP'.
2008-08-06 12:26:57.19 spid5 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\model.mdf' may be incorrect.
2008-08-06 12:26:57.27 spid8 Starting up database 'msdb'.
2008-08-06 12:26:57.27 spid8 udopen: Operating system error 3(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\msdbdata.mdf.
2008-08-06 12:26:57.27 spid8 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\msdbdata.mdf for virtual device number (VDN) 1.
2008-08-06 12:26:57.27 spid8 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\msdbdata.mdf' may be incorrect.
2008-08-06 12:26:57.27 spid9 Starting up database 'pubs'.
2008-08-06 12:26:57.27 spid9 udopen: Operating system error 3(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\pubs.mdf.
2008-08-06 12:26:57.27 spid9 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\pubs.mdf for virtual device number (VDN) 1.
2008-08-06 12:26:57.27 spid9 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\pubs.mdf' may be incorrect.
2008-08-06 12:26:57.27 spid10 Starting up database 'Northwind'.
2008-08-06 12:26:57.27 spid10 udopen: Operating system error 3(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\dataorthwnd.mdf.
2008-08-06 12:26:57.27 spid10 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\dataorthwnd.mdf for virtual device number (VDN) 1.
2008-08-06 12:26:57.27 spid10 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\dataorthwnd.mdf' may be incorrect.
2008-08-06 12:26:57.27 spid11 Starting up database 'EasyShip'.
2008-08-06 12:26:57.27 spid11 udopen: Operating system error 3(error not found) during the creation/opening of physical device E:\mssql7\DATA\uk_050232.mdf.
2008-08-06 12:26:57.29 spid11 FCB::Open failed: Could not open device E:\mssql7\DATA\uk_050232.mdf for virtual device number (VDN) 1.
2008-08-06 12:26:57.30 spid9 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\pubs_log.ldf' may be incorrect.
2008-08-06 12:26:57.30 spid5 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\modellog.ldf' may be incorrect.
2008-08-06 12:26:57.30 server SQL server listening on TCP, Shared Memory, Named Pipes.
2008-08-06 12:26:57.30 server SQL Server is ready for client connections
2008-08-06 12:26:57.63 spid8 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\msdblog.ldf' may be incorrect.
2008-08-06 12:26:57.85 spid11 Device activation error. The physical file name 'E:\mssql7\DATA\uk_050232.mdf' may be incorrect.
2008-08-06 12:26:57.85 spid11 Device activation error. The physical file name 'E:\mssql7\DATA\uk_050232_log.LDF' may be incorrect.
2008-08-06 12:26:57.85 spid10 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\dataorthwnd.ldf' may be incorrect.
2008-08-06 12:26:57.91 spid5 Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


Has anybody managed this type of recovery successfully ??
Luzi62
Luzi62
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 381
Hi,
when i see this error

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\model.mdf' may be incorrect.
2008-08-06 12:26:57.27 spid8 Starting up database 'msdb'.
2008-08-06 12:26:57.27 spid8 udopen: Operating system error 3(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL$EASYSHIP\data\msdbdata.mdf.

i think your original sql server was on the defaul path on C drive.
after restoring the master db the sql server search the msdb on the default location. i think this can only succeed if the installation is exactly the same

regards
felix
MANU-J.
MANU-J.
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 8766
Use WITH MOVE parameter under restore database statement and specify the path that is available(I think you want tp place master files in E drive here).

Manu
wsullivan66
wsullivan66
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 19
Hi,
Felix you were right, soon as i changed the paths all was recoverable
Thanks for your help
Regards
Wayne
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search