Installing a database from mdf.

scascarini's Avatar

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

    Feodor Fitsner's Avatar

    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.

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

    scascarini's Avatar

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

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

    cottsak's Avatar

    @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.
    
  4. 4 Posted by scascarini on 12 Feb, 2015 03:31 PM

    scascarini's Avatar

    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"
    
  5. 5 Posted by cottsak on 13 Feb, 2015 12:07 AM

    cottsak's Avatar

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

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

    cottsak's Avatar

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

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

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

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac

Recent Discussions

22 Mar, 2019 07:18 PM
22 Mar, 2019 02:20 PM
22 Mar, 2019 11:00 AM
22 Mar, 2019 02:51 AM
22 Mar, 2019 12:46 AM

 

21 Mar, 2019 08:34 PM
21 Mar, 2019 05:38 PM
21 Mar, 2019 06:16 AM
20 Mar, 2019 11:39 PM
20 Mar, 2019 09:03 PM
20 Mar, 2019 08:55 PM