Problem trying to create a database+datafile in a specific directory.

• Duran

  SSChampion

  Points: 13259

  More actions

  June 21, 2016 at 12:49 am

  #327379

  Hello,

  I am trying to create a script that will create a database, a datafile with a particular naming convention, and place it in a directory that is created, also using a naming convention, and put it all in the primary filegroup. But I am having difficulties, I feel like I am almost there but I'm missing something or other and have hit a dead end. Looking for some advice or ideas on where I am going wrong.

  ---------------------------------------------------------------

  Import-Module "SqlPs" -DisableNameChecking

  $DatafileName = $DBName +"_D1.mdf"

  $LogFileName = $DBName +"_L1.ldf"

  $InstanceName="MyInstanceName"

  $DBServerName="DBServerName"

  $DrvLetter = "E"+':\'

  $InstanceName = "InstanceName"

  $AppID = "ABC"

  $AppName = "Connect4"

  $DBName="TESTDB"

  $DataFile = New-Item -ItemType Directory -path ("$DrvLetter"+'SQLDATA\MSSQL$'+"$InstanceName"+'\'+"$AppID"+'\'+"$AppName"+'\'+"$DBName"+'\'+"$DatafileName")

  $LogFile = New-Item -ItemType Directory -path "$DrvLetter"+'SQLLOGS\MSSQL$'+"$InstanceName"+'\'+"$AppID"+'\'+"$AppName"+'\'+"$DBName"+'\'+"$LogfileName")

  $BakFile = New-Item -ItemType Directory -path ("$DrvLetter"+'SQLBAK\MSSQL$'+"$InstanceName"+'\'+"$AppID")

  $Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "$DBServerName\$InstanceName"

  #Create The Database

  $DB = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database ($Server, $DBName)

  #$Server.Databases[$DBName] ####I thought this line would tell Powershell what database I wanted to work with, but I think it just gives out info on that database.

  $fg = New-Object -TypeName Microsoft.SqlServer.Management.Smo.FileGroup ($DB, 'PRIMARY')

  $SqlData.FileName = ("$DrvLetter"+'SQLDATA\MSSQL$'+"$InstanceName"+'\'+"$AppID"+'\'+"$AppName"+'\'+"$DBName"+'\'+"$DatafileName")

  $SqlData = New-Object -TypeName Microsoft.SqlServer.Management.Smo.DataFile -ArgumentList ($fg, $DatafileName)

  $SqlData.Create()

  $DB.Create()

  $Error[0]|Format-List -Force

  ----------------------------------------------------

  The error I am currently getting is...

  Exception : System.Management.Automation.MethodInvocationException: Exception calling "Create" with "0" argument(s): "Create failed for DataFile 'TESTDB_D1.mdf'. " --->

  Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for DataFile 'TESTDB_D1.mdf'. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException:

  Cannot create DataFile '[TESTDB_D1.mdf]' if parent is not yet created.

  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()

  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringCollection& createQuery, ScriptingPreferences& sp)

  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

  --- End of inner exception stack trace ---

  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

  at CallSite.Target(Closure , CallSite , Object )

  --- End of inner exception stack trace ---

  at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo

  memberInfo)

  at CallSite.Target(Closure , CallSite , Object )

  at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)

  at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)

  at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

  TargetObject :

  CategoryInfo : NotSpecified: (:) [], MethodInvocationException

  FullyQualifiedErrorId : FailedOperationException

  ErrorDetails :

  InvocationInfo : System.Management.Automation.InvocationInfo

  ScriptStackTrace : at <ScriptBlock>, C:\Users\MyName\Documents\TrialNError2.ps1: line 28

  PipelineIterationInfo : {}

  PSMessageDetails :

  ...The line ' Cannot create DataFile '[TESTDB_D1.mdf]' if parent is not yet created.', but I'm not sure how to deal with this and Google is being a bit shy. The database still gets created, but using the SQL server default file locations.