June 30, 2008 at 3:23 pm
I am building a new development server using SQL Server 2005, SP2. Production server also is the same build.
I wanted to get it set up with the same security as the production server, so I grabbed the latest backup of the master directory and restored it to the development box.
Unfortunately, when I tried to start the instance, it is failing. While it is complaining that all of the user databases are missing, it fails completely when trying to bring the tempdb database online. All of the related messages in the error log are below, but the basic problem is that the production system has tempdb running on a different file path than what I have on the development box. It seems like this shouldn't be that difficult to correct, but I can't seem to find the right information to address it. Any help would be much appreciated.
2008-06-30 16:06:41.01 spid9s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'G:\tempdb.mdf '.
2008-06-30 16:06:41.12 spid9s Error: 17207, Severity: 16, State: 1.
2008-06-30 16:06:41.12 spid9s FCB::Open: Operating system error 3(The system cannot find the path specified.) occurred while creating or opening file 'G:\tempdb.mdf '. Diagnose and correct the operating system error, and retry the operation.
2008-06-30 16:06:41.12 spid9s Error: 17204, Severity: 16, State: 1.
2008-06-30 16:06:41.12 spid9s FCB::Open failed: Could not open file G:\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2008-06-30 16:06:41.12 spid9s Error: 5120, Severity: 16, State: 101.
2008-06-30 16:06:41.12 spid9s Unable to open the physical file "G:\tempdb.mdf ". Operating system error 3: "3(The system cannot find the path specified.)".
2008-06-30 16:06:41.12 spid9s Error: 1802, Severity: 16, State: 4.
2008-06-30 16:06:41.12 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2008-06-30 16:06:41.12 spid9s Error: 5123, Severity: 16, State: 1.
2008-06-30 16:06:41.12 spid9s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'G:\tempdb.mdf '.
2008-06-30 16:06:41.12 spid9s 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.
2008-06-30 16:06:41.12 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
July 1, 2008 at 1:23 am
[font="Verdana"]Hi,
When you copy master db's physical file from one server to another server, sysaltfiles table will be updated with respect to the old server and if you start sql service using this master you will returned with the error message you have show. To establish this successfully, try the below steps.
*) Start SQLServer in single user mode using /m /f /T3806
*) Detach all the user db and system db's except master. Use the script "Detach User Databases".. Include msdb, model and tempdb in the script.
*) Once detached, you need to attach msdb, model and tempdb databases from the new location using sp_attach_db proc
*) After attaching all the system db's successfully you can restart your sql server in normal mode
*) After that attach the user db's
*) Fix all the orpha ID's using sp_change_users_login procedure.[/font]
Regards..Vidhya Sagar
SQL-Articles
July 1, 2008 at 6:49 am
Thanks for the help, that got me back on track.
Scott
July 1, 2008 at 7:29 am
Vidya sagar,
Good explanation of the solution even i have also learnt a lot.
Thanks vidya
July 1, 2008 at 8:13 am
This is how you are supposed to do it: http://msdn.microsoft.com/en-us/library/ms345408.aspx?s=1%5B/url%5D
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply