x

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,

more ▼

asked May 03, 2010 at 02:33 PM in Default

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 03, 2010 at 03:46 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

Thanks Grant !!
May 03, 2010 at 04:16 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 03, 2010 at 02:42 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

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."
May 03, 2010 at 03:00 PM DaniSQL
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.
May 03, 2010 at 03:04 PM DaniSQL
@DaniSQL - what are you hoping to gain by moving the Master Database?
May 04, 2010 at 04:50 AM Fatherjack ♦♦
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.
May 04, 2010 at 09:45 AM Grant Fritchey ♦♦
@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.
May 04, 2010 at 10:15 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x101
x19

asked: May 03, 2010 at 02:33 PM

Seen: 6926 times

Last Updated: May 03, 2010 at 03:16 PM