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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Recovery a SQL 2000 server on an alternate...

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

Rate Topic

Display Mode

Topic Options

Author

Message

wsullivan66

Posted Wednesday, August 06, 2008 7:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 1:51 PM
Points: 3, 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 ??

Post #547514

Luzi62

Posted Wednesday, August 06, 2008 9:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 21, 2011 8:57 AM
Points: 276, 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

Post #547644

MANU-J.

Posted Wednesday, August 06, 2008 10:09 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 1,658, Visits: 8,564

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

Post #547691

wsullivan66

Posted Thursday, August 07, 2008 4:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 1:51 PM
Points: 3, Visits: 19

Hi,
Felix you were right, soon as i changed the paths all was recoverable
Thanks for your help
Regards
Wayne

Post #548164

« Prev Topic | Next Topic »

Permissions