Questions tagged [smo]
SMO stands for SQL Management Objects (and is technically called "Shared" Management Objecs by at least one installer).
37
questions
2
votes
1
answer
395
views
Powershell SMO changing database context from master when setting database to SINGLE_USER
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
[System....
- 1,741
5
votes
1
answer
320
views
How to check Query Store settings on model database?
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 ...
- 3,500
2
votes
2
answers
1k
views
SMO : The login already has an account under a different user name, when calling TransferData()
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 (...
- 71
0
votes
2
answers
88
views
Extended properties are not copied using SMO
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 ...
- 71
-1
votes
1
answer
354
views
Is there any possibility to use SMO objects to copy a database from MS SQL Server 2014 Express (server address) to localhost?
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 ...
- 71
2
votes
2
answers
218
views
SMO, Powershell, & Failed Logins
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 ...
- 166
2
votes
0
answers
59
views
How to set STATISTICS_INCREMENTAL option ON for an index in SQL Server using c#?
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 ...
- 368
1
vote
0
answers
61
views
Add signature to stored procedure using PowerShell SMO
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'
...
- 131
2
votes
0
answers
119
views
SQLSMO Column Dependencies
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 ...
- 121
2
votes
3
answers
1k
views
How can the System databases in SQL Server 2016 be moved using Powershell?
I'm trying to move the system databases using Powershell ie. without using any T-SQL.
Using SMO :
Install-Module SQL-SMO
$smo = New-SMO -ServerName localhost
$smo.databases["TempDB"]....
- 115
9
votes
2
answers
3k
views
SMO, SSMS are slow for management of SQL Server in Docker when connecting to localhost
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 ...
- 765
3
votes
1
answer
11k
views
Missing assembly when running PowerShell script using SMO
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 ...
- 33
7
votes
1
answer
213
views
Gathering Read Routing information using SMO is inaccurate in SQL Server 2016
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 ...
- 3,983
3
votes
2
answers
420
views
Using SMO from PowerShell, is there a way to state that a jobstep output file should be appended and not overwritten?
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 ...
- 7,071
7
votes
2
answers
2k
views
Why can't C# SMO see extended properties on a column but Powershell SMO can?
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 ...
- 385
1
vote
1
answer
930
views
TSQL Solution to backup ALL databases on ALL instances of a server?
I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But ...
- 311
6
votes
2
answers
1k
views
Are cursors/While loops the only way to make administrative changes to multiple databases?
I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in ...
- 311
4
votes
2
answers
8k
views
Handle expired SQL login's passwords in SQL Server
Is it possible to change the password for a login with an expired password using SQL Server Management Objects (SMO) programming?
Many times during working hours I got this message from a production ...
- 6,301
4
votes
1
answer
202
views
How long does it take for powershell to restore a SQL Backup?
I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name:
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)")
$...
- 53
2
votes
1
answer
550
views
SMO Copy database or remove all non-system database objects
I have c# SMO script which copy all database objects to existed empty database or to a new database.
But this approach has two problems
Existed database is not empty, manual intervention is needed.
...
- 1,039
1
vote
2
answers
5k
views
Trying to use SMO Transfer Object to transfer a database from one instance to another
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 ...
- 1,510
2
votes
1
answer
2k
views
Script Table Definition in Powershell
I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*. I'm having problems adding the columns and the clustered column store index.
Here's ...
- 933
14
votes
6
answers
45k
views
What is the most effective way to discover all running instances of SQL Server using PowerShell?
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 ...
- 356
2
votes
1
answer
536
views
Set method to change StartMode property using SqlServer.Management.Smo.Wmi.Service
I'm trying to change SQL server Configuration manager settings using Microsoft.SqlServer.Management.Smo.Wmi.Service. Microsoft provided with a list of methods & properties here. I used the ...
- 261
3
votes
1
answer
1k
views
Why can't I script out CLR assemblies with SMO in Powershell?
I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most ...
- 441
4
votes
1
answer
17k
views
Cannot read property Collation.This property is not available on SQL Server 7.0
I'm not able to add administrator role in SQL Server 2008 R2.
When I try to add new user it throws me the following error:
Cannot read property Collation.This property is not available on SQL ...
Sheeja
4
votes
2
answers
13k
views
Using SQL Server SMO with a Port number
In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using System.Data.SqlClient....
user507
1
vote
1
answer
626
views
Why SMO's Transfer tries to login into linked server when scripting?
Consider this code sample:
var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword";
var serverConnection = ...
- 165
0
votes
1
answer
3k
views
What is the best practices to backup and restore full and partial database with SQL Server 2012 and C#?
How can I do the following with C#:
Completely backup an existing database.
Partially backup an existing database.
Completely restore an existing database from a backup file.
Partially restore an ...
- 139
2
votes
2
answers
6k
views
Shrinking log files with SMO
I know I can use the following t-SQL to shrink a log file:
BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);
How do I do the same with SMO? I tried:
$server = ...
- 695
6
votes
1
answer
1k
views
Why am I missing some output?
Archiving to avoid ID Exhaustion
We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in ...
- 2,298
5
votes
2
answers
1k
views
SQL Server SMO and PowerShell formatting
I have probably been looking at this for too long to figure this out...
What is the easiest way I could get this exported to a CSV format?
Would it be better to dump this to a table in a database ...
user507
4
votes
1
answer
2k
views
SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints
I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output ...
5
votes
2
answers
3k
views
Can I Run Two Full Backups on SQL Server 2005 at the Same Time?
Running SQL Server Express 2005 here. We are creating several programs which run a backup on the database using Server Management Objects (SMO) mostly through C# (.NET). We have separate processes ...
- 278
6
votes
4
answers
7k
views
Script Contents of SQL Server Database From a Batch File
I want to be able to script the schema of a given database into a .sql file from the Windows command prompt. Basically, I want to execute the "Generate Scripts" feature of Management Studio ...
- 7,250
6
votes
1
answer
1k
views
SQL Server SMO Method "Discover()"
I think I just found the coolest thing every with SQL Server SMO and PowerShell. I was asked to get a script together to find the owner of every object in an instance of SQL Server. I found a script ...
user507
0
votes
2
answers
792
views
Is command BACKUP asynchronous operation
I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location.
Is it possible that multiple backup operations are running at the same time? I have ...
- 6,662