August 6, 2008 at 7:48 am
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 ??
August 6, 2008 at 9:30 am
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
August 6, 2008 at 10:09 am
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
August 7, 2008 at 4:47 am
Hi,
Felix you were right, soon as i changed the paths all was recoverable
Thanks for your help
Regards
Wayne
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply