Can not start SQL SERver service after system database restore

Question

question

DaniSQL asked May 3, '10

Can not start SQL SERver service after system database restore

Hi,

I had to move a sql server 2005 sp3 x86 system databases to 2008 sp1 x64 environment. Since I cant install "can't restore a 2005 system database on a 2008 system" I install fresh copy of 2005 somewhere else with the same build as the source server and then restore the system backups. Then I did an in place upgrade to 2008 sp1. Then i took a backup of the 2008 sp1 system databases and restore it to the destination. MSDB restore was successful. then I did Master DB restore which was also successful. All went great so far! However, after master database restore when I changed the service to multi-user and try to restart the service fails and this is the error log I get.

2010-05-03 12:12:06.81 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) 
    Mar 29 2009 10:11:52 
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

2010-05-03 12:12:06.81 Server      (c) 2005 Microsoft Corporation.
2010-05-03 12:12:06.81 Server      All rights reserved.
2010-05-03 12:12:06.81 Server      Server process ID is 2200.
2010-05-03 12:12:06.81 Server      System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2010-05-03 12:12:06.81 Server      Authentication mode is MIXED.
2010-05-03 12:12:06.81 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2005\MSSQL\Log\ERRORLOG'.
2010-05-03 12:12:06.81 Server      This instance of SQL Server last reported using a process ID of 2228 at 5/3/2010 12:03:09 PM (local) 5/3/2010 4:03:09 PM (UTC). This is an informational message only; no user action is required.
2010-05-03 12:12:06.81 Server      Registry startup parameters: 
     -d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2005\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2005\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2005\MSSQL\DATA\mastlog.ldf
2010-05-03 12:12:06.82 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-05-03 12:12:06.82 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2010-05-03 12:12:06.89 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2010-05-03 12:12:06.94 Server      Node configuration: node 0: CPU mask: 0x0000000000000003 Active CPU mask: 0x0000000000000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-05-03 12:12:07.01 spid7s      Starting up database 'master'.
2010-05-03 12:12:07.35 spid7s      CHECKDB for database 'master' finished without errors on 2010-03-14 15:58:11.657 (local time). This is an informational message only; no user action is required.
2010-05-03 12:12:07.38 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQL2005'.
2010-05-03 12:12:07.39 spid7s      SQL Trace ID 1 was started by login "sa".
2010-05-03 12:12:07.40 spid7s      Starting up database 'mssqlsystemresource'.
2010-05-03 12:12:07.41 spid7s      The resource database build version is 10.00.2531. This is an informational message only. No user action is required.
2010-05-03 12:12:07.65 spid10s     Starting up database 'model'.
2010-05-03 12:12:07.65 spid7s      Server name is 'HQS-SQLJOBS\SQL2005'. This is an informational message only. No user action is required.
2010-05-03 12:12:07.66 spid7s      Informational: No full-text supported languages found.
2010-05-03 12:12:07.66 spid13s     Starting up database 'msdb'.
2010-05-03 12:12:07.66 spid14s     Starting up database 'SiteServer'.
2010-05-03 12:12:07.66 spid13s     Error: 17204, Severity: 16, State: 1.
2010-05-03 12:12:07.66 spid13s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2010-05-03 12:12:07.66 spid14s     Error: 17204, Severity: 16, State: 1.
2010-05-03 12:12:07.66 spid14s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SiteServer.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2010-05-03 12:12:07.66 spid13s     Error: 5120, Severity: 16, State: 101.
2010-05-03 12:12:07.66 spid13s     Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2010-05-03 12:12:07.67 spid14s     Error: 5120, Severity: 16, State: 101.
2010-05-03 12:12:07.67 spid14s     Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SiteServer.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2010-05-03 12:12:07.67 spid13s     Error: 17207, Severity: 16, State: 1.
2010-05-03 12:12:07.67 spid13s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-05-03 12:12:07.67 spid14s     Error: 17207, Severity: 16, State: 1.
2010-05-03 12:12:07.67 spid14s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SiteServer_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2010-05-03 12:12:07.67 spid13s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf" may be incorrect.
2010-05-03 12:12:07.67 spid13s     Starting up database 'NOPR_BU'.
2010-05-03 12:12:07.67 spid14s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SiteServer_log.LDF" may be incorrect.
2010-05-03 12:12:07.67 spid14s     Starting up database 'PDM'.
2010-05-03 12:12:07.67 spid13s     Error: 17204, Severity: 16, State: 1.
2010-05-03 12:12:07.67 spid13s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NOPR_BU.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2010-05-03 12:12:07.67 spid14s     Error: 17204, Severity: 16, State: 1.
2010-05-03 12:12:07.67 spid14s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PRED_DATA_MATCH.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2010-05-03 12:12:07.67 spid13s     Error: 5120, Severity: 16, State: 101.
2010-05-03 12:12:07.67 spid13s     Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NOPR_BU.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2010-05-03 12:12:07.67 spid14s     Error: 5120, Severity: 16, State: 101.
2010-05-03 12:12:07.67 spid14s     Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PRED_DATA_MATCH.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2010-05-03 12:12:07.68 spid13s     Error: 17207, Severity: 16, State: 1.
2010-05-03 12:12:07.68 spid13s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NOPR_BU_1.LDF'. Diagnose and correct the operating system error, and retry the operation.
2010-05-03 12:12:07.68 spid14s     Error: 17207, Severity: 16, State: 1.
2010-05-03 12:12:07.68 spid14s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PRED_DATA_MATCH_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-05-03 12:12:07.68 spid13s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NOPR_BU_1.LDF" may be incorrect.
2010-05-03 12:12:07.68 spid13s     Starting up database 'PET_BU'.
2010-05-03 12:12:07.68 spid14s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PRED_DATA_MATCH_log.ldf" may be incorrect.
2010-05-03 12:12:07.68 spid13s     Error: 17204, Severity: 16, State: 1.
2010-05-03 12:12:07.68 spid13s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PET_BU.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2010-05-03 12:12:07.68 spid13s     Error: 5120, Severity: 16, State: 101.
2010-05-03 12:12:07.68 spid13s     Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PET_BU.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2010-05-03 12:12:07.68 spid13s     Error: 17207, Severity: 16, State: 1.
2010-05-03 12:12:07.68 spid13s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PET_BU_1.LDF'. Diagnose and correct the operating system error, and retry the operation.
2010-05-03 12:12:07.68 spid13s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PET_BU_1.LDF" may be incorrect.
2010-05-03 12:12:07.69 spid10s     Error: 17204, Severity: 16, State: 1.
2010-05-03 12:12:07.69 spid10s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2010-05-03 12:12:07.69 spid10s     Error: 5120, Severity: 16, State: 101.
2010-05-03 12:12:07.69 spid10s     Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2010-05-03 12:12:07.69 spid10s     Error: 17207, Severity: 16, State: 1.
2010-05-03 12:12:07.69 spid10s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2010-05-03 12:12:07.69 spid10s     File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf" may be incorrect.
2010-05-03 12:12:07.69 spid10s     Error: 945, Severity: 14, State: 2.
2010-05-03 12:12:07.69 spid10s     Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2010-05-03 12:12:07.69 spid10s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2010-05-03 12:12:07.69 spid10s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Now I uninstalled and installed the instance and I need help on how to avoid the same problem again. Any help is appreciated and let me know if more information is required.

Thanks,

sql-server-2008 restore system-databases

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-03T15:46:52Z

Grant Fritchey answered May 3, '10

The very first error should give you the indications you need:

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).2010-05-03 12:12:07.66 spid14s

It sounds like the new server had things installed in different locations than the original server. You can't really swap the master database around like that. Usually the best approach is to move your logins using scripts and then attach or restore the user databases.

At this point you're probably going to have to rebuild the master database.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-05-03T14:42:45Z

Grant Fritchey answered May 3, '10

It's pretty simple, you can't restore a 2005 system database on a 2008 system. Here's a link to MS describing the issue.

9

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL commented · May 03, 2010 at 03:00 PM

Thats why I mentioned what I did to workaround the problem on the second sentence "So I install fresh copy of 2005 somewhere else with the same build as the source server and then restore the system backups. Then I did an in place upgrade to 2008 sp1. Then i took a backup of the 2008 sp1 system databases and restore it to the destination."

0 ·

DaniSQL commented · May 03, 2010 at 03:04 PM

My issue now is how to move a master backup to a server with the same build and version. I already move the msdb db, but when I restore the master backup the database service failed and I get the above error.

0 ·

Fatherjack ♦♦ commented · May 04, 2010 at 04:50 AM

@DaniSQL - what are you hoping to gain by moving the Master Database?

0 ·

Grant Fritchey ♦♦ commented · May 04, 2010 at 09:45 AM

I assume they're trying to retain the logins. Cuts down on the number of steps for a migration. Unfortunately it's not really a step you can skip.

0 ·

DaniSQL commented · May 04, 2010 at 10:15 AM

@FatherJack: At first I just needed to move the msdb database as a way of moving the jobs and packages. But I figured this would be a good opportunity to restore the master database since I might need it one day during disaster. Which I did to other 2005 database. Then I upgraded it to 2008 and I restore it to the final destination but after the restore the service wont start throwing Error: 5120 and Error: 17207 and that's when i asked your help.

0 ·

Show more comments

question