CoNetrix

Blog: SQL Server 2005

I recently ran into a problem trying to restore a SQL Server 2005 database to a Windows XP machine.  The database back up was created on a Windows Vista machine and I thought that the different OS versions was the culprit.  However, it turned out to be related to SQL server instances and folder paths.  Here is full error I received when I attempted to restore the database using SQL Server Management Studio:

Restore failed for Server 'localhost\sqlexpress'.  (Microsoft.SqlServer.Express.Smo)

Additional information:
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\[my database name].mdf'. (Microsoft.SqlServer.Express.Smo)

I searched the Internet using the error message and found several posts stating that it was a problem with the privileges of the user account that my SQL Server Express service was running as. I hadn't changed the account it was running as and I had restored other databases in the past, but I checked the SQL Server Configuration Manager anyway.  As I suspected the service was still running as the default account (Network Service), so that wasn't it.   [more]

After I couldn't find a quick fix on the Internet I decided to look around the options in the Restore Database window.  It turns out the problem was with the paths under the restore options.  The backup was trying to restore the .mdf and .ldf files to the c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\ directory.  The instance of SQL Server I was working with was storing all it's data files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\.  Once I changed the paths for the database file and log file to be the same as where my other database files were being stored the restore ran fine with no errors.  For additional reference, here is an article that explains the naming of folders for SQL Server 2005 instances: http://weblogs.sqlteam.com/tarad/archive/2006/06/07/10114.aspx

Networking, Backup, Windows Vista, SQL Server, Windows XP, SQL Server 2005,
 

I was trying to install a hotfix package onto a SQL Server and continued to get Error 29528. Upon inspection into the WindowsUpdate.log file, I noticed this line: “Error 29528. The setup has encountered an unexpected error while Setting Internal Properties”. After some Google searching, I stumbled upon a Microsoft KB article that helped out quite a bit. http://support.microsoft.com/kb/925976/

The workaround resolution involves the following steps:

For a stand-alone installation of SQL Server 2005

  • Remove the following registry subkeys that store SID settings:
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\SQLGroup
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\AGTGroup
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\FTSGroup
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\ASGroup

[more]Note In these registry subkeys, MSSQL.X is a placeholder for the corresponding value on a specific system. You can determine MSSQL.X on a specific system by examining the value of the MSSQLSERVER registry entry under the following registry subkey:

  • Reinstall the SQL Server 2005 service pack or the SQL Server 2005 hotfix package.

For some reason, the local SIDs had been changed or removed or something and SQL just needed to figure out what was going on.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\

Networking, SQL Server, SQL Server 2005,
 

There is a known issue with SQL Server 2005 SP2 that occurs if you have moved the system databases to another location. Specifically, the mymssqlsystemresource database. If the mymssqlsystemresource transaction log has been moved from its default location, the installer for SP2 does not recognize this. The installer places the new mymssqlsystemresource.ldf file in the same directory as the mymssqlsystemresource.mdf file regardless of whether this is where you have moved the .ldf file to. After the SP2 install, SQL Server services will not start. To fix the issue, simply copy the .ldf file from the directory where mymssqlsystemresource.mdf lives and place it in the updated location and restart the SQL Server services.

Networking, SQL Server, SQL Server 2005,
 

The SQL 2005 installer expects the installation media to be in a specific layout.  If the directory structure is incorrect the error message you receive during setup is: "There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information."

The behavior I saw was that the SQL server itself would install, but the Management Studio installation failed.

From http://support.microsoft.com/kb/916760:
This problem is most likely to occur if you start the SQL Server 2005 installation from a folder on a network share or on a hard disk when the folder was copied from the SQL Server 2005 installation CDs.

WORKAROUND
To work around this problem, set the folders in the correct layout for the SQL Server 2005 installation. [more]

The SQL Server 2005 installation uses the following two folders:

  • Servers
  • Tools

These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools. The Servers folder contains all the files that are required to install major SQL Server 2005 components, such as database engine. The Tools folder contains tools components and Books Online for SQL Server 2005.

Networking, SQL Server, SQL Server 2005,