Creating a SQL Server DB via Powershell: Error calling Create with 0 arguments

Question

My employer wants to automate the creation and setup of our application database. For now, I'm simply trying to create a db on my local machine's SQL Server instance. The code:

$inst = "DESKTOP-9K3NITB\MSSQLSERVER01"
$dbname = "mydb1"
 
# change to SQL Server instance directory  
Set-Location SQLSERVER:\SQL\$inst  
 
# create object and database  
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -Argumentlist $inst, $dbname  
$db.Create()

That code yields the following error from Powershell

Exception calling "Create" with "0" argument(s): "Create failed for Database 'mydb1'. "
At line:9 char:1
+ $db.Create()
+ ~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

The weird thing here is that when I then go into SQL Server Management Studio, I can see that the database was indeed created. So I guess I'm just confused about why I'm getting an error, and what exactly the error means, since it looks like the code did what it was supposed to. Any help would be appreciated.

EDIT: The SQL Server log message:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 600 seconds. Working set (KB): 144216, committed (KB): 295820, memory utilization: 48%.

Answer 1

You're not examining the error details. Try something like

$inst = "DESKTOP-9K3NITB\MSSQLSERVER01"
$dbname = "mydb1"
 
# change to SQL Server instance directory  
Set-Location SQLSERVER:\SQL\$inst  
 
# create object and database  
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -Argumentlist $inst, $dbname  

try
{
  $db.Create()
}
catch
{
  Write-Host $Error[0].Exception
}

You'll see something like:

System.Management.Automation.MethodInvocationException: Exception calling "Create" with "0" argument(s): "Create failed for Database 'mydb1'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for Da
tabase 'mydb1'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Database 'mydb1' already exis
ts. Choose a different database name.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(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.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)