Home → Questions →

Installing a database from mdf.

• Edit

scascarini

08 Jul, 2014 03:18 PM

I'm trying to create database from a mdf file on my build server.

I have found some powershell which should do the job

Function installDatabase() {
   $mdfFilename = "AdventureWorks_Data"
   $ldfFilename = "AdventureWorks_Log"
   $DBName = "AdventureWorks"
   Add-PSSnapin SqlServerCmdletSnapin* -ErrorAction SilentlyContinue 
   If (!$?) {Import-Module SQLPS -WarningAction SilentlyContinue} 
   If (!$?) {"Error loading Microsoft SQL Server PowerShell module. Please check if it is installed."; Exit}
   $attachSQLCMD = @"
USE [master] 
GO 
CREATE DATABASE [$DBName] ON (FILENAME = '$mdfFilename.mdf'),(FILENAME = '$ldfFilename.ldf') for ATTACH
GO 
"@
   Invoke-Sqlcmd $attachSQLCMD -QueryTimeout 3600 -ServerInstance '(local)\SQL2012SP1' 
}

However when I run it I get the following error

Invoke-Sqlcmd : Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

To fix that it seems that I need to modify the powershell_ise.exe.config file. However when I attempt to modify or replace that file in
'C:\Windows\System32\WindowsPowerShell\v1.0\' I get

Access to the path 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell_ise.exe.config' is denied.

Presumably because the script is not running as administrator.

So

1. Is modifying powershell_ise.exe.config the correct thing to do within the AppVeyor environment in order to run invoke-sqlcmd ?
   
2. If not what's the correct approach ?
   
3. If yes how can I run the script as administrator (I've tried this from stack-overflow http://stackoverflow.com/questions/7690994/powershell-running-a-com... - second answer - it ran but I still got the denied access message) so that I can modify the file?

thanks

1. Support Staff 1 Posted by Feodor Fitsner on 08 Jul, 2014 03:40 PM

   

   It is admin. "appveyor" user account is a member of "Administrators" group. This because by default C:\Windows\System32\WindowsPowerShell\v1.0 has "Read" only permissions for both SYSTEM and Administrators. Presumably, you should first change ACLs on that folder/file (say, using cacls utility) and then modify the file.

   Alternatively, I'd use sqlcmd and sp_attach_db command instead of PS.

   • Edit

2. 2 Posted by scascarini on 09 Jul, 2014 03:16 PM

   

   OK got that working with sqlcmd. Thanks for the suggestion a lot easier than trying to wrestle with SQLPS !

   • Edit

3. 3 Posted by cottsak on 12 Feb, 2015 02:53 PM

   

   @scascarini what was the script you used for sqlcmd? I'm having trouble using sp_attach_db via sqlcmd.

   The error is

   Msg 3415, Level 16, State 2, Server APPVYR-WIN20122\SQL2014, Line 1
   Database 'ControllerTests.Web' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.
   

   • Edit

4. 4 Posted by scascarini on 12 Feb, 2015 03:31 PM

   

   This works for me

   sqlcmd -S (local)\SQL2012SP1 -Q "Use [master]; CREATE DATABASE [AdventureWorks] ON (FILENAME = 'C:\projects\<proj>\AdventureWorks_Data.mdf'),(FILENAME = 'C:\projects\<proj>\AdventureWorks_Log.ldf') for ATTACH"
   

   • Edit

5. 5 Posted by cottsak on 13 Feb, 2015 12:07 AM

   

   Awesome! Did you have to do anything to the permissions on those mdf and ldf files to make them accessible to your script?

   • Edit

6. 6 Posted by cottsak on 13 Feb, 2015 01:11 AM

   

   @scascarini I got my stuff working thanks to your help. Cheers

   http://help.appveyor.com/discussions/questions/943-my-localdb-conne...

   • Edit

7. Ilya Finkelshteyn closed this discussion on 25 Aug, 2018 01:54 AM.