Upgrading from SQL 2005 to SQL 2008 R2 – Fails if default data folder doesn’t exist

This might apply to other upgrades as well but I would this today. If the default data and log directories don’t exist then the upgrade fails. The reason is that the upgrade tries to create a database in the default locations and that fails and so your upgrade fails.

I got the following in the SQL Error Log

2010-11-06 20:31:46.55 spid6s      Directory lookup for the file "F:\MSSQL.1\MSSQL\DATA\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
2010-11-06 20:31:46.56 spid6s      Error: 1802, Severity: 16, State: 1.
2010-11-06 20:31:46.56 spid6s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2010-11-06 20:31:46.56 spid6s      Error: 912, Severity: 21, State: 2.
2010-11-06 20:31:46.56 spid6s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent90_sysdbupg.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2010-11-06 20:31:46.57 spid6s      Error: 3417, Severity: 21, State: 3.
2010-11-06 20:31:46.57 spid6s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

I had initially created this server with two extra drives for the databases but never got round to sorting out the folders. Thus the reason for the failure. The challenge is that you can’t change the default settings without connecting. Well you can.

You need to find the correct registry hive look at http://msdn.microsoft.com/en-us/library/ms143547.aspx, i.e.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

You can then find the DefaultData and DefaultLog string values.

image

Make sure that what ever you set it to the SQL Service account has access to it.

Published Sunday, November 7, 2010 8:40 AM by simonsabin

Comments

No Comments