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

avatar image

DaniSQL
4.9k 33 39 43

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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.

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:

x2091
x124
x25

asked: May 03, 2010 at 02:33 PM

Seen: 9269 times

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

Copyright 2016 Redgate Software. Privacy Policy