K
Kevin Bulgrien
This is a general FYI to anyone having difficulty with restoring the XP
Embedded Component Database.
Even though the XPe documentation on the XP Embedded Studio media says it is
easy to backup the database, I have found that restoring the database is not
so
easy to do successfully. Even the Feature Pack 2007 dbrestore.vbs
(discussed here:
http://blogs.msdn.com/embedded/arch...-and-restoring-your-xp-embedded-database.aspx
) has troubles under some circumstances, though after finding the information
below, I really do not really understand what merit that script provides as
it does not backup the Repository folder.
We found is that some forms of archiving the database and repositories can
strip critical permissions that prevent the restored files from being usable.
When this happens, attempts to manage/open the database can result in errors
like this:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Unable to open the physical file "C:\Windows Embedded
Data\MantisSQLDB_Data.mdf". Operating system error 5: "5(Access is denied.)".
Unable to open the physical file "C:\Windows Embedded
Data\MantisSQLDB_Data_log.LDF". Operating system error 5: "5(Access is
denied.)".
Database 'MantisSQLDB' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
File activation failure. The physical file name "C:\Windows Embedded
Data\MantisSQLDB_Data_log.LDF" may be incorrect. (Microsoft SQL Server,
Error: 5120)
For help, click:
http://go.microsoft.com/fwlink?Prod...42&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476
------------------------------
Or like this:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Database 'MantisSQLDB' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
(Microsoft SQL Server, Error: 945)
For help, click:
http://go.microsoft.com/fwlink?Prod...042&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476
------------------------------
The root cause is that a backup/restore mechanism was used that did not
preserve all of the permissions on the .ldf and .mdf files, and since
"C:\Windows Embedded Data" does not have these critical permissions they are
not automatically inherited by files placed in the directory as a result of
restore operations. Naturally, this problem will not occur if a selected
backup mechanism does preserve Windows file permissions.
The .ldf and .mdf files appear to require full-control by the "NETWORK
SERVICE" group. Oddly, I cannot add "NETWORK SERVICE" rights to files using
the Security tab on the file properties dialog though it is possible to add
"NETWORK" and "SERVICE" group rights individually to files, and this seems to
work, but does not match the original rights the files had prior to backup).
The workaround found was to add "NETWORK SERVICE" group permissions to the
"C:\Windows Embedded Data" directory. This causes the rights to be inherited
by the files placed in that directory. It should be noted that doing so does
change the rights placed on that directory by a clean installation of the
development tools.
Of course, as a reminder in case it is not obvious, do not mess with the
"C:\Windows Embedded Data\MantisSQLDB_Data.mdf" or "C:\Windows Embedded
Data\MantisSQLDB_Data_log.LDF" files without first stopping the SQL server.
This can be done a number of ways. One command-line example is:
net stop sqlbrowser
net stop mssqlserver
For reference, it should be noted that in this case, the SQL Server is the
Microsoft SQL Server 2005 Express Edition, so your mileage may vary if
another SQL server is in use.
Embedded Component Database.
Even though the XPe documentation on the XP Embedded Studio media says it is
easy to backup the database, I have found that restoring the database is not
so
easy to do successfully. Even the Feature Pack 2007 dbrestore.vbs
(discussed here:
http://blogs.msdn.com/embedded/arch...-and-restoring-your-xp-embedded-database.aspx
) has troubles under some circumstances, though after finding the information
below, I really do not really understand what merit that script provides as
it does not backup the Repository folder.
We found is that some forms of archiving the database and repositories can
strip critical permissions that prevent the restored files from being usable.
When this happens, attempts to manage/open the database can result in errors
like this:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Unable to open the physical file "C:\Windows Embedded
Data\MantisSQLDB_Data.mdf". Operating system error 5: "5(Access is denied.)".
Unable to open the physical file "C:\Windows Embedded
Data\MantisSQLDB_Data_log.LDF". Operating system error 5: "5(Access is
denied.)".
Database 'MantisSQLDB' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
File activation failure. The physical file name "C:\Windows Embedded
Data\MantisSQLDB_Data_log.LDF" may be incorrect. (Microsoft SQL Server,
Error: 5120)
For help, click:
http://go.microsoft.com/fwlink?Prod...42&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476
------------------------------
Or like this:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Database 'MantisSQLDB' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
(Microsoft SQL Server, Error: 945)
For help, click:
http://go.microsoft.com/fwlink?Prod...042&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476
------------------------------
The root cause is that a backup/restore mechanism was used that did not
preserve all of the permissions on the .ldf and .mdf files, and since
"C:\Windows Embedded Data" does not have these critical permissions they are
not automatically inherited by files placed in the directory as a result of
restore operations. Naturally, this problem will not occur if a selected
backup mechanism does preserve Windows file permissions.
The .ldf and .mdf files appear to require full-control by the "NETWORK
SERVICE" group. Oddly, I cannot add "NETWORK SERVICE" rights to files using
the Security tab on the file properties dialog though it is possible to add
"NETWORK" and "SERVICE" group rights individually to files, and this seems to
work, but does not match the original rights the files had prior to backup).
The workaround found was to add "NETWORK SERVICE" group permissions to the
"C:\Windows Embedded Data" directory. This causes the rights to be inherited
by the files placed in that directory. It should be noted that doing so does
change the rights placed on that directory by a clean installation of the
development tools.
Of course, as a reminder in case it is not obvious, do not mess with the
"C:\Windows Embedded Data\MantisSQLDB_Data.mdf" or "C:\Windows Embedded
Data\MantisSQLDB_Data_log.LDF" files without first stopping the SQL server.
This can be done a number of ways. One command-line example is:
net stop sqlbrowser
net stop mssqlserver
For reference, it should be noted that in this case, the SQL Server is the
Microsoft SQL Server 2005 Express Edition, so your mileage may vary if
another SQL server is in use.