From new drive, create folders in root. These will be used in restore script.
Execute Restore Script
This script will create a new database on DBMS from restoring a backup and maps filegroups to new drive. It will also rename file groups to reflect new database name.
This script will need to be run separately in 3 steps. Note the steps are differentiated by comments.
Install the Microsoft Access Database Engine 2010 Redistributable, hotfix and service pack update.
Please note that the hotfix must be installed BEFORE the service pack.
All items should be installed via "run as admin".
All files can be found in SVN under /Trunk/DataCenter/SQL
1) Install the engine: AccessDatabaseEngine_x64.exe
2) Verify obsolete MSO.DLL version is 14.0.4760.1000 as found in the folder below
C:\Program Files\Common Files\Microsoft Shared\OFFICE14
3) Install the hotfix: office2010-kb2516475-fullfile-x64-glb.exe
Verify new MSO.DLL version is 14.0.5137.500 ( properties / details )
4) Install the service pack : accessdatabaseengine2010sp1-kb2460011-x64-fullfile-en-us.exe
Verify MSO.DLL version is still 14.0.5137.500 is retained.
Update SQL via SSMS
sp_configure 'show advanced option', 1
sp_configure 'Ad Hoc Distributed Queries', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
-- The two statements below are for future use if we ever want to insert into an existing XLS file
-- None of our servers currently have this functionality and it has not been tested.
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
Update registry via REGEDIT and add the key below.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
Where DisallowAdhocAccess is a REG_DWORD set to 0
Please note that the path "\MSSQL10_50.MSSQLSERVER\" will change by SQL version.
You can find the exact path by locating the "AllowInProcess" keys that were created by the above script.
Verify that the query works.
'SELECT * FROM [Sheet1$]')
REBOOT and verify the query still works.