Connecting to an older sql version's Integration Services instance will throw the Class Not Registered error and the Properties for a sql agent SSIS job step will not show (e.g. DataSources). This is due to the SMO objects installed with SSMS...they have to match the version of sql (e.g. SSMS 19 is v16, so works with SQL 2022 SSIS instances). If I have SSMS 19 (SMO v16) installed, can I also install the SMOs for older versions to avoid the class not registered error when connecting from SSMS 19 to SSIS 2019, 2016, etc.? The workaround is to also install the older versions of SSMS (18 is SMO v15 for SQL 2019, etc.) but that's cumbersome. Thanks!

There has recently been [a lot of talk](https://curiousaboutdata.com/2024/08/18/t-sql-tuesday-177-roundup-managing-database-code/) about putting database code in source control. I have read widely on this topic and there appears to be an unstated assumption that using SMO through PowerShell makes it very easy to do the following simultaneously: 1. Connect to a Microsoft SQL Server. 2. Grab every unit of code in a database (e.g. every table, index, stored procedure, function, view, permission, database config, ... *but not data*) 3. Save them in separate files, so that they're useful for source control. Despite my best efforts, I can find no off-the-shelf SMO code that does this. It really is as if there is a secret SMO script that I cannot find. To me, this suggests that such a script is so easy to write that nobody has bothered publicising it. dbatools has a handful of specialised functions for certain types of object (e.g. Get-DbaDbStoredProcedure), but finding every required dbatools function for this and correctly piping it to Export-DbaScript would be a big job. I'm also aware of some Redgate tools, **but I'm specifically asking about SMO**. So, to put my question in an answerable form: What parts of the SMO make it very easy to write a script that simultaneously satisfies all of my three numbered points above?

I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem to find all the instances. 1) Use WMI $srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName $instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}} return $instances 2) Use remote registry (as with Get-SQLInstance[1] ) The biggest problem I'm running into is that not all of the servers that I know about are running with the SQL Server WMI provider nor are all of them allowing remote registry. Is there a third method? I can use Remote Desktop to access all the servers but I'm looking at approximately 30 machines and would like to avoid manual steps if possible. This only needs to work for SQL Server 2008 and higher and while it would be nice to know about the other SQL Server services (SSIS/SSAS/SSRS) my main focus is on SQL Server itself.

I like to use Export-DbaScript to export to folders that are named C:\TargetFolder\Database\Schema. Often, I don't already have a Database\Schema folder, which will cause Export-DbaScript to fail to write to that folder. Is there any way to make Export-DbaScript create the folder if it doesn't already exist? I'm expecting something like a -Force parameter, but I have not found it in the documentation. I'm doing

Export-DbaScript `
  -InputObject $_ `
  -NoPrefix `
  -NoClobber `
  -FileName "$($MyLocation)\$($_.Database)\$($_.Name).sql

When the path doesn't exist, I get System.IO.DirectonaryNotFoundExceptions. I'm on version 2.1.18

I am trying to set a database to single user using SQL Server SMO and have the code below. This code is an abridged excerpt from a larger restore script

$Credential = Get-Credential



$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "MY-SERVER1";
$srv.ConnectionContext.NonPooledConnection = $true;
$srv.ConnectionContext.ConnectTimeout = 0;
$srv.ConnectionContext.StatementTimeout = 0;

$srv.ConnectionContext.LoginSecure = $false;
$srv.ConnectionContext.set_Login($Credential.username);
$srv.ConnectionContext.set_SecurePassword($Credential.password);
$srv.ConnectionContext.DatabaseName = "master";

$srv.ConnectionContext.Connect()

($srv.ConnectionContext.ExecuteWithResults("SELECT DB_NAME(),@@SERVERNAME")).Tables # verify database and servername we are connected to

$srv.Databases.Item("MyDB").UserAccess = "Single";
$srv.KillAllProcesses("MyDB");

try
{
    $srv.Databases.Item("MyDB").Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately);
}
catch
{
    Write-Host $_.Exception.GetBaseException().Message;
    Write-Host "";
}

$srv.ConnectionContext.Disconnect();

The account I pass into $credential has the following permissions in SQL Server:

GRANT CREATE ANY DATABASE TO [MyUser]
GRANT VIEW SERVER STATE TO [MyUser] 
GRANT ALTER ANY DATABASE TO [MyUser] 
GRANT ALTER ANY CONNECTION TO [MyUser]
ALTER SERVER ROLE [dbcreator] ADD MEMBER [MyUser]

When I run the Powershell code above, it fails on the $srv.Databases.Item("MyDB").Alter line with the error >The server principal "MyUser" is not able to access the database "MyDB" under the current security context. when I open a profiler trace, I can see that it is running USE [MyDB] and MyUser doesn't exist in that database which explains the error. if I wanted to do this in Management studio, I would run the ALTER DATABASE statement against the master database:

USE master;
ALTER DATABASE [MyDB]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

which works when run as MyUser in SSMS. How do I stop the SMO alter command trying to switch the database context to the MyDB my user doesn't have access to? If possible, I am looking to amend the existing code using SMO, rather than re-write using Invoke-SqlCmd or something else as this script is used extensively in production. The script was written originally using SMO as that was the best way identified to Set a database to single user mode and Restore the database using the same SPID

Query Store can be enabled on the *model* database and ensures that every new database has the same settings as the *model* database. The GUI option is missing But it can be enabled using TSQL ALTER DATABASE model SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); Since there is no GUI, I can't check the default settings there. Using TSQL again USE model; select * from sys.database_query_store_options; Returns empty result When I create a new database (that uses the model as a template and query the settings, it shows me the result) create database TestQs; go use TestQs; select * from sys.database_query_store_options; Also, the settings must be saved somewhere because when I change the *Query Store* options, the change is propagated to new databases ALTER DATABASE model SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 22); I've tried to use SMO to find those options, but no luck. $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'localhost' $sqlServer.Databases['TestQs'].QueryStoreOptions But the same query for the *model* database yields nothing $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'localhost' $sqlServer.Databases['model'].QueryStoreOptions Is there a way to check *Query Store* settings on the *model* database without creating a new database and checking there?

I need to implement the following requirement. I have setup a remote sql server express, certain users shall be able to create databases on remote server and assign other users to have access on it (read,write). Users shall be able to see only the databases created by them or in which they have rights. First I have created an empty database on remote server => dbo for new database is x, then I've created a different login with: USE [master] GO CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] Then deny view to any database: USE master; GO DENY VIEW ANY DATABASE TO [username];  Then create new user for login: USE dbName; CREATE USER [username] FOR LOGIN [username] ; GO Give db owner role: EXEC sp_addrolemember 'db_owner', [username] Give permission to see database (here is a little bit tricky because in ssms it can view all the databases but it can have access only on the mapped ones) USE master GRANT VIEW ANY DATABASE TO [username]; Now on my given database I have 2 users one is username and the other is dbo which is mapped on the user that created the database and is different form username. After that I'm using SMO to transfer this databases from remote server to localhost and while executing transferDatabase.TransferData(); I'm getting the following error "The login already has an account under a different user name." When I create the transfer instance I set also the property CopyAllUsers= true. How can I fix this? I've found out that if i make the following changes USE [master] GO CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] Then deny view to any database: USE master; GO DENY VIEW ANY DATABASE TO [username];  USE master; GO ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username]; GO Now username sees in ssms only the databases on which is mapped and the transfer will probably work. But now I cannot distinguish between the first creator of the database and the recently granted user. For one database I should grant dbowner permissions to more than one user. So if then I create another login and I execute: USE master; GO ALTER AUTHORIZATION ON DATABASE::dbName TO [anotherUser]; GO Now username can not access the database.

I'm using the following code to copy a database from server to localhost using SMO. The code works well except the fact the extended properties are not copied. //Set Source SQL Server Instance Information Server sourceServer = new Server(sourceServerName); //Set Destination SQL Server Instance Information Server destinationServer = new Server(destinationServerName); try { //Using windows authentication sourceServer.ConnectionContext.LoginSecure = true; #region Optional - Using SQL Server authentication #endregion\ sourceServer.ConnectionContext.Connect(); //Using windows authentication destinationServer.ConnectionContext.LoginSecure = true; #region Optional - Using SQL Server authentication #endregion destinationServer.ConnectionContext.Connect(); InitServer(sourceServer); InitServer(destinationServer); //Set Source Database Name [Database to Copy] Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName]; //Set Destination Database Name Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName); databaseDestination.Create(); //Set Transfer Class Source Database Transfer transferDatabase = new Transfer(databaseSource); //transferDatabase.CopyAllUsers = true; transferDatabase.CopyAllObjects = false; transferDatabase.CopyAllSchemas = true; transferDatabase.CopyAllUserDefinedDataTypes = true; transferDatabase.CopyAllTables = true; transferDatabase.CopyData = true; transferDatabase.CopyAllStoredProcedures = true; transferDatabase.DestinationServer = destinationServer.Name; transferDatabase.DestinationDatabase = databaseDestination.Name; transferDatabase.TemporaryPackageDirectory = @"C:\DATA\"; transferDatabase.Options.WithDependencies = true; transferDatabase.Options.DriAll = true; transferDatabase.DropDestinationObjectsFirst = true; transferDatabase.Options.ContinueScriptingOnError = true; transferDatabase.TransferData(); } catch (Exception ex) { log.Error(ex.Message); } finally { if (sourceServer.ConnectionContext.IsOpen) { sourceServer.ConnectionContext.Disconnect(); } if (destinationServer.ConnectionContext.IsOpen) { destinationServer.ConnectionContext.Disconnect(); } }

I need to implement in c# a functionality for copying a certain database from server address to localhost (I'm aware of the backup/restore option - I have this implemented when I want to export and import databases that are stored locally). I've tried the following source code but when I tried to run the transferData I get an error "The Integration Services component is not installed or you do not have permission to use it.". I'm using SQL Express 2014.I have installed also SharedManagementObjects and SQLSysClrTypes.msi. I've also noticed that in my c# solution I have some references to Smo assemblies like NuGet installation but they are for SQL Server 2008. Locally I can find some smo assemblies in C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies. Should I reference them in my project? I've tried the following code, but I'm getting exception in transferDatabase.TransferData();: Server sourceServer = new Server(sourceServerName); Server destinationServer = new Server(destinationServerName); try { sourceServer.ConnectionContext.LoginSecure = true; sourceServer.ConnectionContext.Connect(); destinationServer.ConnectionContext.LoginSecure = true; destinationServer.ConnectionContext.Connect(); Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName]; Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName); databaseDestination.Create(); Transfer transferDatabase = new Transfer(databaseSource) { CopyAllObjects = false, CopyAllSchemas = true, //Copy all user defined data types from source to destination CopyAllUserDefinedDataTypes = true, //Copy all tables from source to destination CopyAllTables = true }; //Copy all constraints transferDatabase.Options.DriAllKeys = true; //Copy all defaults transferDatabase.Options.DriDefaults = true; //Drops the existing tables transferDatabase.DropDestinationObjectsFirst = true; //Copy data of all source tables to destination tables //It actually generates INSERT statement for destination transferDatabase.CopyData = true; //Copy all stored procedure from source to destination transferDatabase.CopyAllStoredProcedures = true; //specify the destination server name transferDatabase.DestinationServer = destinationServer.Name; //specify the destination database name transferDatabase.DestinationDatabase = databaseDestination.Name; //TransferData method transfers the schema objects and data //whatever you have specified to destination database transferDatabase.TemporaryPackageDirectory = @"C:\Data\"; transferDatabase.Options.ContinueScriptingOnError = true; transferDatabase.TransferData(); } catch (Exception ex) { log.Error(ex.Message); } finally { if (sourceServer.ConnectionContext.IsOpen) { sourceServer.ConnectionContext.Disconnect(); } if (destinationServer.ConnectionContext.IsOpen) { destinationServer.ConnectionContext.Disconnect(); } }

The schema tables are owned by dbo and another schema name. The code below is not moving over any tables. Is there something else that I need to add to make it transfer the tables? There are no stored procedures or views that need to get moved. $xfr1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer($prodServerDB.Databases[$devDatabase]) #Set this objects properties $xfr1.DestinationLoginSecure = $false $xfr1.DestinationServer = $devServer $xfr1.DestinationLogin = $devUsername $xfr1.DestinationPassword = $devPassword $xfr1.DestinationDatabase = $devDatabase $xfr1.BatchSize = 10485760 $xfr1.CopyData = $true $xfr1.CopyAllTables = $true $xfr1.CopyAllObjects = $true $xfr1.CopyAllDatabaseTriggers = $true $xfr1.CopyAllLogins = $false $xfr1.CopyAllRoles = $false $xfr1.CopyAllUsers = $false $xfr1.CopySchema = $true $xfr1.PreserveDbo = $true $xfr1.PreserveLogins = $true $xfr1.Options.AllowSystemObjects = $false $xfr1.Options.ContinueScriptingOnError = $true $xfr1.Options.Indexes = $true $xfr1.Options.IncludeIfNotExists = $true $xfr1.Options.DriAll = $true $xfr1.Options.SchemaQualify = $true $xfr1.Options.ScriptSchema = $true $xfr1.Options.ScriptData = $true $xfr1.Options.WithDependencies = $true # Script the transfer. Alternatively perform immediate data transfer with TransferData method. # $xfr1.ScriptTransfer() # $xfr1.EnumScriptTransfer() $xfr1.TransferData() ***Current Error Stack below:*** PS C:\powershell_sample_scripts> $Error | select * ErrorRecord : Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details." StackTrace : at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo) at CallSite.Target(Closure , CallSite , Object ) at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) WasThrownFromThrowStatement : False Message : Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details." Data : {System.Management.Automation.Interpreter.InterpretedFrameInfo} InnerException : Microsoft.SqlServer.Management.Common.TransferException: An error occurred while transferring data. See the inner exception for details. ---> System.Data.SqlClient.SqlException: User, group, or role 'user_account' already exists in the current database. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction) at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at CallSite.Target(Closure , CallSite , Object ) TargetSite : System.Collections.ObjectModel.Collection`1[System.Management.Automation.PSObject] Invoke(System.Collections.IEnumerable) HelpLink : Source : System.Management.Automation HResult : -2146233087 PSMessageDetails : Exception : System.Management.Automation.MethodInvocationException: Exception calling "TransferData" with "0" argument(s): "An error occurred while transferring data. See the inner exception for details." ---> Microsoft.SqlServer.Management.Common.TransferException: An error occurred while transferring data. See the inner exception for details. ---> System.Data.SqlClient.SqlException: User, group, or role 'user_account' already exists in the current database. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction) at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at CallSite.Target(Closure , CallSite , Object ) --- End of inner exception stack trace --- at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input) at Microsoft.PowerShell.Executor.ExecuteCommandHelper(Pipeline tempPipeline, Exception& exceptionThrown, ExecutionOptions options) TargetObject : CategoryInfo : NotSpecified: (:) [], MethodInvocationException FullyQualifiedErrorId : TransferException ErrorDetails : InvocationInfo : System.Management.Automation.InvocationInfo ScriptStackTrace : at , C:\powershell_sample_scripts\runMigration.ps1: line 150 at , : line 1 PipelineIterationInfo : {}

**Background** - At my current organization, we've got a SQL Agent Job running a Powershell script, originally based on Script All Server Level Objects to Recreate SQL Server . - This job writes a bunch of scripts that can be used in the event we need to do disaster recovery. - Our version queries a separate database that lists out the servers that should be checked. - Prior to this job, I hadn't used Powershell though I've had other scripting experience (i.e. Python). - We also have all of our servers setup, using the SQL Server Agent, to email the DBAs when we have failed logins. - One of our development servers has a couple of databases on it that are taken offline for some unknown (to me) business purpose. - When our SQL Agent Job runs, it always generates failed login alerts when it comes to those offline databases on that one development server. I've been asked to see if there's someway to have the script skip checking databases that are offline to prevent these false alerts. - I've traced the source of the alerts back to the function below. I believe this bit, if($database.Status -eq 'Normal') was intended to make sure the database is online but it doesn't work. - Based on some other reading online, I tried replacing if($database.Status -eq 'Normal') with if ($database.IsAccessible), but I still got the failed login alert email when running the script.

#Function to write out Database scripts
function ScriptOutDBObjects($serverObject, $objectType)
{
    foreach ($database in $srv.Databases)
    { 
        if($database.Status -eq 'Normal')
        {
            $objectPath = $scriptPath + $objectType +'\'
            if (!(Test-Path -Path $objectPath))
            {
                New-Item -ItemType Directory -Path $objectPath
            }
            $scriptingOptions.FileName = $objectPath + $database.Name.Replace(':','').Replace('\', '_') +'.sql'
            $database.Script($scriptingOptions)
        }
    }
}

- The job is using the Powershell type, though I get the same results when running the code from the Powershell ISE on my local computer testing things out. **Question** Will Microsoft.SqlServer.Management.Smo always try to open the database? If so, is there some flag or something I can pass it to make it not do so?

I need help with programmatically setting the STATISTICS_INCREMENTAL option ON for an index in a table in SQL Server (2016 SP2). I am using Microsoft.SqlServer.Smo, Version=13.0.0.0. I do not see any property in the Index class to enable STATISTICS_INCREMENTAL. I checked Version 140.17283.0 as well. I am trying to periodically update statistics for only a few partitions using the command: UPDATE STATISTICS [Schema].[Table] IX_Table_Index WITH RESAMPLE ON PARTITIONS(4). I enabled the Auto Create Incremental Statistics option at the database level. I just need help with enabled STATISTICS_INCREMENTAL at the index level.

I want to add a signature to a stored procedure using PowerShell SMO. The equivalent command in TSQL is: ADD SIGNATURE TO [myprocedure] BY CERTIFICATE [mycertificate] with password = 'CertificatePW' How do I do this using PowerShell SMO without having to resort to using any form of TSQL?

I'm trying to help a teammate walk object dependencies in SQL Server. I've had *some* success using things like sys.sql_expression_dependencies (and other related DMV/DMFs) but it's falling short due to a lot of weird edge cases (cross database references, inconsistent naming conventions, etc.) I'm looking into using SqlSMO to walk object dependencies, and so far, it's working pretty well; at least for object dependencies. I give it a procedure name, and use a DependencyWalker to tell me things like views, functions and procedures called therein. But now I need to also know which columns are referenced in the proc. So instead of just knowing my inputProc references someTable, I'd like to know that it references someTable.A and someTable.B. So far I haven't been able to find a way to do this using SMO. Anyone know if it can be done? This is an excerpt of C# code which returns the object references; not sure if and how to modify it to include column-level dependencies as well. public static void Walk() { Server server = new Server(@"localhost\koala"); Database db = server.Databases["test"]; Scripter scr = new Scripter(server); scr.Options.WithDependencies = true; scr.Options.NoViewColumns = false; Urn[] urns = new Urn[] { db.StoredProcedures["xdb"].Urn }; DependencyTree dt = scr.DiscoverDependencies(urns, true); DependencyWalker dw = new DependencyWalker(server); DependencyCollection dc = dw.WalkDependencies(dt); foreach (var item in dc) { Console.WriteLine(item.Urn.Value); } }

I'm trying to move the system databases using Powershell ie. **without** using any T-SQL. 1. Using SMO : `Install-Module SQL-SMO $smo = New-SMO -ServerName localhost $smo.databases["TempDB"].PrimaryFilePath= "F:\Tempdb\" $smo.databases["TempDB"].Alter()` gives an error: >'PrimaryFilePath' is a ReadOnly property. The logfile moves fine though. $smo.databases["TempDB"].LogFiles.Filename = "F:\Tempdb\tempdb.ldf" 2. Couldn't locate any specific cmdlets in the SQLServer or DBATools modules either. Copy-DbaDatabase comes closest; but not exactly.

I've been playing around with SMO to check out some properties recently, and have come across a problem when trying to gather information on the read-routing order in SQL Server 2016. With earlier versions the routing list was quite simple, the list would be processed in order presented, but with 2016 they introduced the round-robin algorithm allowing you to have multiple secondary replicas accept the read traffic. While this data is accessible through T-SQL it does not appear as though SMO has been updated to reflect this, which means that you cannot accurately get, or set the configuration that way as it is still a simple string collection. Is there some attribute that I am missing that would allow me to get accurate data for SQL Server 2016? (example SMO call for C# below) It is just the round-robin info that does not seem to be accessible, it lists all of the replicas that are in the read-routing list, it just does not seem to differentiate the various groups of replicas that would exist. Connect bug report. *Connect is dead...* using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; namespace SmoTesting { class Program { static void Main(string[] args) { Console.WriteLine("Enter the servername"); string connectServer = Console.ReadLine(); Console.WriteLine("Enter the AG name"); string agName = Console.ReadLine(); Server srv = new Server(); try { srv = new Server(connectServer); srv.ConnectionContext.StatementTimeout = 60; //timeout after 60 seconds running the query foreach (AvailabilityGroup ag in srv.AvailabilityGroups) { if (ag.Name == agName) { ag.PrimaryReplicaServerName.ToString()); foreach (AvailabilityReplica ar in ag.AvailabilityReplicas) { if (ar.Name.ToString() == "connectServer") { foreach (Object obj in ar.ReadonlyRoutingList) { Console.WriteLine(" {0}", obj); } } } } } } catch (Exception ex) { Console.WriteLine(ex.InnerException.ToString()); } finally { srv.ConnectionContext.Disconnect(); } Console.WriteLine("press a key"); Console.Read(); } } }

**TL;DR:** When connecting to my SQL Server Docker container via a name that resolves to the IPv6 loopback (::1), SMO calls are really slow. When using 127.0.0.1, they're fast. **** I am trying to learn how to use the Docker image [microsoft/mssql-server-windows-developer](https://hub.docker.com/r/microsoft/mssql-server-windows-developer/) . Per Microsoft’s documentation, this container only exposes port 1433 TCP. docker run -d -p 1433:1433 -e sa_password=Passw0rd! -e ACCEPT_EULA=Y -v C:\dockerdb:C:\dockerdb microsoft/mssql-server-windows-developer I am running the container on Windows 10, and have been successful starting it, authenticating with SQL Server authentication, and running queries against the instance using sqlcmd and SSMS 17.4 on the windows host (connecting to localhost or “.”), and SQL Operations Studio on a mac next door connecting by IP. I see no noticeable performance issues when running queries this way. In SSMS, I can also browse object explorer, but if I try to do something from the right click menu on an object in the object explorer, like open the instance parameters window or attach a database, SSMS does not show a response for about 5-10 minutes, at which point it either displays the window I asked for, or displays this error message: I am also trying to do some [PowerShell scripting against this instance using the SMO Scripter object](https://gist.github.com/NReilingh/813400c9a39bba41ca56d4bd9e0e3948) , and see the same kind of behavior. The PS script loops through objects in the database and scripts them to file, and while it works to gather the list of objects relatively quickly, each individual object takes 5-10 minutes to script—too slow to be usable. I have a hunch that the single exposed port is not enough and that SMO and SSMS are trying to connect in a similar way that is slowing them down. Could it also be that when connecting to localhost, these tools assume there to be other communication channels present that would typically not be firewalled? Are there any additional connection parameters I could be using? Can anyone validate my assumption that SSMS is using SMO or something else to talk to SQL Server? **** **UPDATE:** I'm still investigating, but it's plausible that this is a Docker issue around resource constraints. This is confusing because most of the documentation seems to indicate that Windows Containers don't have any default resource constraints (and these can **not** be set in the Docker for Windows GUI — [only for Linux containers](https://github.com/docker/for-win/issues/754)) , but it seems that in reality, Windows containers running on Windows 10 get a default RAM allocation of 1GB. I'm still trying to figure out how to inspect a running container to see its RAM and CPU allocation, but next I've got to just try increasing those from whatever the defaults are, using docker run parameters. **** **FURTHER UPDATE:** I've been failing to get any kind of reliable metric out of docker that tells me what CPU and memory limits it has in place for the container. [Varying research](https://social.msdn.microsoft.com/Forums/en-US/2e58dc53-c34f-4ef9-bcf1-ccf87209ec6f/docker-windows-container-memory-limit?forum=windowscontainers) indicates that either docker containers don't have a memory limit by default, or that they do and it's 1GB, but all I can verify at the moment is that docker stats says the SQL container is only _using_ between 750 and 850 meg, and when I try to add a run parameter to set available memory to 4 gb, it errors out. So I stopped following that thread of inquiry and went for a different gut check: entering an interactive powershell session on the running container and then invoking my powershell script linked above from _inside_ the container. **Running inside the container there was no issue. It blazed through 2780 objects in just a couple of minutes.** I think this confirms that the problem is with the container/host boundary, so I'm going to see if I can open up that UDP port. **UPDATE:** Opening port 1434 UDP didn't help. **** **MORE UPDATES—Workaround Achieved, Not a resource constraint issue:** There appear to be [issues](https://github.com/docker/for-win/issues/1094) related to setting large memory allocations for windows containers — I was receiving similar errors for 3g and 2g, but eventually was able to start the container with 1.5g, and I DID see a difference in the docker stats for the container that (I think) confirms it was running with a default allocation of 1GB. On default settings, the PRIV WORKING SET stat (which I can't find any documentation for, but my best guess is it's RAM) is between 700MiB and 850MiB. With docker run —memory="1.5g" set, it's around 1.0GiB. So it did expand, but appears to be leaving more of the allocation free than it did before. I interpret this (perhaps incorrectly) to mean that this server (which is running absolutely NO load and has NO user databases) is not under memory pressure. I checked the max server memory setting to confirm that it is set at the default maximum of 2PiB. Then things got weird. I'm still testing things by running my powershell script from various locations. Fast inside the container, slow on the host. Then I RDP'd to another windows machine on the network, and ran the script from THAT machine, connecting to my Windows 10 host by IP. **And it was FAST!** This seems to support the theory that when connecting to something that's supposed to be localhost, SMO is trying to connect to SQL Server using something other than port 1433 TCP, that waits for a very long timeout before falling back to the TCP connection. I decided to try validating this theory by entering a hosts file entry to refer to localhost by a name other than localhost: 127.0.0.1 dockersucks I connected in SSMS to dockersucks instead of localhost or ".", and immediately things were faster. Navigating object explorer was like usual, and opening panels like attach database or server properties happened as quickly as normal. And, when I ran my powershell script from the windows 10 host using this alias as the server name, it was fast as well. I added this update to the question instead of an answer since I'm still looking for an explanation of why this is occurring, and if there's a way to fix it for connections to "localhost" by that name.

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties. The C# code: var x = col.ExtendedProperties.Count; var NPI = col.ExtendedProperties["NPI"].Value; bool npi = bool.Parse(NPI.ToString()); The PowerShell code: Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" $server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME $server.Databases[""].Tables[""].Columns[""].ExtendedProperties | Select Name, Value, State I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get: Name Value State ---- ----- ----- NPI False Existing Does anyone have any ideas as to why this could be happening? ### Additional Information In the C# code I open up a DataReader on a table using: sourceServer.ConnectionContext.ExecuteReader() to retrieve the data from the table. I then go into a while loop with DataReader and inside that while loop I have: foreach (Column col in sourceTable.Columns) { StringBuilder cleanData = CleanseColumn(col, dr[col.Name].ToString()); sbvalues.Append("'" + cleanData + "', "); } When I step through the foreach, the sourceTable variable has its extended property, but the col column variable does not.

I wrote a script which takes database name, restored DB name, backup source, data file location and log file location as parameters and performs database restore on SQL Server instance. Script works fine when ran from PowerShell ISE, but it is throwing missing assemblies error when ran from PowerShell console or term is not recognized as the name of a cmdlewhen error when ran throught SQL Agent job as CmdExec or PowerShell type. I have tried to add path to assemblies with Add-Type in script, but there is no assemblies named Microsoft.SqlServer.Management.Smo.Server, Microsoft.SqlServer.Management.Smo.Restore... etc. I am out of ideas as PS newb. Any advice, idea how to solve this problem to be able to run this script within SQL Agent job step? I am executing the script in ISE and console with command .\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log Executing the script in SQL Agent job C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Script\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log Script Param( [parameter(Mandatory=$true)] [String] $DatabaseName, [parameter(Mandatory=$true)] [String] $NewDatabaseName, [parameter(Mandatory=$true)] [String] $SourceDir, [parameter(Mandatory=$true)] [String] $DataDir, [parameter(Mandatory=$true)] [String] $LogDir) $file = $DatabaseName+".bak" $fileFullPath = $SourceDir+"\"+$file $fullDataFile = $DataDir+"\"+$DatabaseName+".mdf" $fullLogFile = $LogDir+"\"+$DatabaseName+"_log.ldf" $ServerName = $env:computername $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $ServerName $restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore' $backupDeviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem' ($fileFullPath, 'File') $restore.Database = $NewDatabaseName $restore.ReplaceDatabase = $true $restore.Devices.Add($backupDeviceItem) $fileList = $restore.ReadFileList($server) $dataFileNumber = 0 foreach ($file in $fileList) { $relocateFile = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile' $relocateFile.LogicalFileName = $file.LogicalName if ($file.Type -eq 'D') { if($dataFileNumber -ge 1) { $suffix = "_$dataFileNumber" } else { $suffix = $null; } $relocateFile.PhysicalFilename = "$DataDir\$DatabaseName$suffix.mdf" $dataFileNumber++ } else { $relocateFile.PhysicalFileName = "$LogDir\$DatabaseName"+"_log.ldf" } $restore.RelocateFiles.Add($relocateFile) | Out-Null } $restore.SqlRestore($server) Exit Error from PS console New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: verify that the assembly containing this ty pe is loaded. At C:\Automation\RestoreDB_Script.ps1:32 char:12 + $restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore' + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.BackupDeviceItem]: verify that the assembly containin g this type is loaded. At C:\Automation\RestoreDB_Script.ps1:33 char:21 + ... eviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDevi ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand The property 'Database' cannot be found on this object. Verify that the property exists and can be set. At C:\Automation\RestoreDB_Script.ps1:34 char:1 + $restore.Database = $NewDatabaseName + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound The property 'ReplaceDatabase' cannot be found on this object. Verify that the property exists and can be set. At C:\Automation\RestoreDB_Script.ps1:35 char:1 + $restore.ReplaceDatabase = $true + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound You cannot call a method on a null-valued expression. At C:\Automation\RestoreDB_Script.ps1:36 char:1 + $restore.Devices.Add($backupDeviceItem) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\Automation\RestoreDB_Script.ps1:37 char:1 + $fileList = $restore.ReadFileList($server) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\Automation\RestoreDB_Script.ps1:67 char:1 + $restore.SqlRestore($server) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull Message from SQL Agent job step Executed as user: domain\account. ...id not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'New-Object : The term 'New-Object' is not recognized as the name of a cmdlet, f ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'unction, script file, or operable program. Check the spelling of the name, or i f a path was included, verify that the path is correct and try again. At C:\Scripts\RestoreDB_Script.ps1:31 char:11 + $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $Ser ... + ~~~~~~~~~~' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: ' + CategoryInfo : ObjectNotFound: (New-Object:String) [], CommandN ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: ' otFoundException ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: ' + FullyQualifiedErrorId : CommandNotFoundException ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: ' ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: ver ' A job step r... Process Exit Code 0. The step succeeded.

I deploy multiple SQL Server jobs to a fleet of servers using a PowerShell script. I can create the job steps, success fail action, and even the output file. This all works on multiple servers. What I cannot determine is how to specify that the **output file is to be appended** to instead of overwritten. The following is the section from my main script. $Job is populated in a previous step. $JobStep = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($Job, 'MyJobStepName') $JobStep.ID = 1 $JobStep.SubSystem = 'PowerShell' $Command = 'dir d:\' $JobStep.Command = $Command $JobStep.OnSuccessAction = 'GotoNextStep' $JobStep.OnFailAction = 'GotoNextStep' $JobStep.OutputFileName = 'D:\MyJobFile.txt' $JobStep.Create() I have been using this MSDN page as a reference. I modified one of the steps using SQL Server Management Studio and traced the result. This looks promising. I can issue a SQL command after I create the jobsteps. EXEC msdb.dbo.sp_update_jobstep @job_id=N'12156dcb-c8cd-4267-901a-55175dd44960', @step_id=1 , @flags=2