I'm in the process of moving a postgres database to a new server. I have number of large tables, partitioned by month, each contains several 10's of millions of rows. I have a powershell script to do the following.... - Copy the backup file (for a single partition) from archive location (using robocopy) - load the file back into the database using psql. - drop the backup file. - loop back to the beginning and move onto the next partition. Copying the file from the archive takes maybe 10 minutes, loading it typically 40 minutes, then it moves onto the next partition. **The problem** is that the PowerShell console seems to occasionally get 'stuck' after loading a partition. Occasionally, a partition seems to be taking too long (say an hour) I check the windows task manager, and none of the processes seem to be doing anything. I check the dashboard in pgAdmin4 (no locks, most sessions idle). Then I check the record count for the partition being loaded - the record count seems to be static (still the same 5 minutes later). So as far as I can tell the partition has been loaded successfully, and the database is mostly idle. But the powershell console that's running the load session hasn't changed, then I shift focus to the powershell console, press return a couple of times and suddenly it bursts into life. The console tells me it's moved onto the partition, the task-manager indicates that the powershell process and postgres process are now running again, pgAdmin4 also indicates the database has sprung back into life. The powershell script logs to screen using Write-host and the current time between each step, so I always know what the most recent step was. My conclusion is that the interaction between powershell and psql is somehow 'broken' - powershell hasn't worked out that psql has finished and doesn't know to move onto the next step. Other info - there's no sign of any errors (that I can spot) all the tables seem to get populated correctly (eventually) and the script works fine on all the smaller partitions. This only happens on about 1 partition in 10, the only cure seems to be pressing enter a couple of times. This isn't a critical problem, occasionally hitting enter a couple of times is a viable solution, but an understanding of what might be going on (or even a solution) would be much appreciated.

I am using a Powershell script to generate scripts of my SQL database objects (Tables, Views, StoredProcedures etc.). It was working fine in testing and still works on 2 out of the 3 servers being included. Recently the one server began returning this error: > Error: Exception calling "Script" with "1" argument(s): "The View > '[dbo].[spt_values]' cannot be scripted as its data is not > accessible.". The Powershell script $date_ = (date -f yyyyMMdd) $ServerName = "ServerA" #If you have a named instance, you should put the name. $path = "\\core.windows.net\home\Rep\SQL"+"$date_" $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName $IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. $ExcludeSchemas = @("sys","Information_Schema") $so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') $dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases. foreach ($db in $dbs) { $dbname = "$db".replace("[","").replace("]","") $dbpath = "$path"+ "\"+"$dbname" + "\" if ( !(Test-Path $dbpath)) {$null=new-item -type directory -name "$dbname"-path "$path"} foreach ($Type in $IncludeTypes) { $objpath = "$dbpath" + "$Type" + "\" if ( !(Test-Path $objpath)) {$null=new-item -type directory -name "$Type"-path "$dbpath"} foreach ($objs in $db.$Type) { if ($ExcludeSchemas -notcontains $objs.Schema ) { $ObjName = "$objs".replace("[","").replace("]","") $OutFile = "$objpath" + "$ObjName" + ".sql" $objs.Script($so)+"GO" | out-file $OutFile } } } } The View causing the error is dbo.spt_values. It's in System Views in the Master database. The same View is on my other two servers and does not cause an error. I checked the Properties and cannot find a difference between the servers that work and the one that does not. I tried restarting SQL services to see if maybe something was blocking access to it, but there was no improvement. The Powershell is being executed from a Powershell component in an SSIS package.

A user.sql file which contains USE [database] GO -- Legacy users cleanup DROP USER IF EXISTS [dw]; etc... is located at the repository: https://company.visualstudio.com/Database/_git/Azure?path=etc ... I have a powershell script running on a VM that restores the database and I need it to remove users from the newly restored db using the above user.sql file. Is there a way to access the file directly from the .ps1 script, read the code from it and execute it? If not, what would be the steps to make it work? Thanks and regards.

I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account. I'm running this cmd: Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddMinutes(-15)) I want to exclude these: InstanceID : 146788 SqlMessageID : 0 Message : The job failed. The Job was ***invoked by User domain\user***. The last step to run was step 3 (packagename Pkg). StepID : 0 StepName : (Job outcome) but include these InstanceID : 146789 SqlMessageID : 0 Message : The job failed. The Job was ***invoked by User domain\serviceaccount***. The last step to run was step 3 (packagename Pkg). StepID : 0 StepName : (Job outcome)

as a follow up from this question below: Run a PowerShell script on a different server - from inside a SQL Server job there you can see that for me tu run a powershell script from inside a sql server job, I need to create a proxy. Both the powershell script and the proxy script are on that link. what the powershell script does is to delete old backup files on a remote server. the question that I still have is: for the whole thing to work on windows server 2012, I could find not other way than granting Administrator on the remote box to the Proxy account. as you can see on the picture below. another alternative was to create a powershell script that do both, backups and deletes, and create a sql server job with the following code: Is this a powershell limitation when running on a remote server? Are there other ways to work around it, without having to grant admin to the proxy account? PowerShell Basics: Managing Remote Servers & Sessions

I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect Here is the mysqldump command on my ps1 file. $command = [string]::format(""{0}" -u {1} -p{2} -h {3} --default-character-set=utf8mb4 --quick --master-data=2 --single-transaction --routines --databases test1 --add-drop-database --result-file="{5}" ", $mysqlDumpLocation, $databaseUsername, $databasePassword, $databaseIp, $database.DatabaseName, $saveFilePath); But when I try to remove the {2} which is the DB password, then execute the ps1 file. When it prompt me for the password, it goes through.

How can i automate DIfferential restore using dbatools Powershell I have configured FUllbackup migration from powershell using dbatools and using multi thread I am able to restore 4.5TB under 4 hours. As for the downtime I want to also automate the Differential Restore using powershell. Is this possible?

Trying to export SQL Objects via DBAtools in Powershell. Get-DbaAgentJob -SqlInstance 'TestServer01' -Job 'mydatabase-daily-backup' | Export-DbaScript -FilePath C:\temp\mydatabase-daily-backup.sql The same script working fine and exporting the job details. But, not working on a few servers, and returns the below error/warning > WARNING: [06:05:42][Get-DbaAgentJob] Failure | The value's length for key 'Data Source' exceeds its limit of '128'.

I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac to my local SQL DB using Restore-Database, but below fails with "the media family on device is incorrectly formatted". Restore-Database -dbName "MyDB" -backupFile "C:\MyAzureBackup.bacpac" Is there a way to get this working with Restore-Database, or is there an alternative command that will do this?

I am trying to figure out how to restore a database from one managed SQL instance to another. I'm following the tutorials, but I keep running into inscrutable error messages. Here's my command:

Restore-AzSqlInstanceDatabase `
    -Name "SomeDatabase" `
    -InstanceName "our-oltp-dev" `
    -ResourceGroupName "dev-managedsqlinstances" `
    -PointInTime "4/7/2020 12:00:00" `
    -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
    -TargetInstanceName "our-oltp-sandbox" `
    -TargetResourceGroupName "sandbox-managedsqlinstances"

Here's the output:

PS C:\WINDOWS\system32> Restore-AzSqlInstanceDatabase `
    -Name "SomeDatabase" `
    -InstanceName "our-oltp-dev" `
    -ResourceGroupName "dev-managedsqlinstances" `
    -PointInTime "4/7/2020 12:00:00" `
    -TargetInstanceDatabaseName "SomeDatabase_FROM_DEV" `
    -TargetInstanceName "our-oltp-sandbox" `
    -TargetResourceGroupName "sandbox-managedsqlinstances"
Restore-AzSqlInstanceDatabase : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:1
+ Restore-AzSqlInstanceDatabase `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Restore-AzSqlInstanceDatabase], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.Azure.Commands.Sql.ManagedDatabase.Cmdlet.RestoreAzureRmSqlManagedDatabase

It's a copy-and-paste from the Azure docs; so I'm not sure what I'm doing wrong. Any help would be appreciated.

I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. I assumed that the error was being caused by differences in the module path, so I explicitly set the path via $env:PSModulePath to match the path of my powershell session where the code runs fine. I am pretty new to powershell, so any help you could provide would be appreciated I am just doing some testing right now, so everything is running locally on my desktop. I am running SQL Server 2012.

Using SQL Server 2017 Developer Edition The command I'm using is: >New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName host.doman.com -KeySpec KeyExchange -FriendlyName SQLCert The error message is: >New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeySpec'. My powerShell version is:

PS C:\Windows\system32> $PSversionTable

Name                           Value
----                           -----
PSVersion                      4.0
WSManStackVersion              3.0
SerializationVersion           1.1.0.1
CLRVersion                     4.0.30319.42000
BuildVersion                   6.3.9600.18773
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0}
PSRemotingProtocolVersion      2.2

I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table names as parameters. The procedures are for a data validation process to see what is not aligned to our specifications. These all check for different things: the "validity" of specific values, formatting etc. That said they all have a different output, including the number of columns. I am attempting to automate the load, the stored procedure runs, and the placement of the result sets into a spreadsheet. I have written a Powershell script to load the files into the DB using dbatools, and then run the "root" stored procedure that runs the other 12 or so. I have found myself a bit stuck attempting to export this data without doing 12+ separate stored procedure calls into separate CSVs or inserting the results into a "final" table that includes header names in the rows... I will do that if needed, but this output is typically sent to clients for review; forcing them to open up to 12 CSVs for a review is cumbersome and having one table is not very clean given how much the columns differ. TSQL is my jam and I am not super experienced with PS. If I am being dumb, please point me to a relevant resource that I can make myself not dumb. Any suggestions are appreciated. Thanks

double quotes are used in sql server, and their behaviour can be defined by the option SET QUOTED_IDENTIFIER ON \ OFF double quotes are also used in powershell . SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods. this is a partial view of a stored procedure that I want to deploy to multiple servers, however, because of the quoted-identifier, or double quotes this character - " - it is not working in powershell, in the visual code that I use to deploy.

DECLARE @WebXConfigurationID INT,
				@stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
				@stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
				@cur_Properties XML;


			PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

			IF (ISNULL(@stored_URL, '')  ISNULL(@cur_URL, ''))
			BEGIN
				PRINT '	- Base URL needs updating'
			END 
			ELSE
			BEGIN
				PRINT '	- Base URL does not need updating'
			END;

			IF (ISNULL(@stored_OpsServiceURL, '')  ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
			BEGIN
				PRINT '	- OPS Service URL needs updating'
				SET @cur_Properties.modify('replace value of (/properties[1] //OPSServiceURL[1] /text())[1]  with sql:variable("@stored_OpsServiceURL")')
			END 
			ELSE
			BEGIN
				PRINT '	- OPS Service URL does not need updating'
			END;

this is the error message:

"' in expression or statement.
At C:\sp_PosRestore.ps1:636 char:114
+ ... ServiceURL[1] /text())[1]  with sql:variable("@stored_OpsServiceURL")')
+                                                 ~~~~~~~~~~~~~~~~~~~~~
The splatting operator '@' cannot be used to reference variables in an expression. '@stored_OpsServiceURL' can be used only as an argument to a command. To reference variables in an expression use      
'$stored_OpsServiceURL'.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

The Powershell code:

$SQLQuery = 
"
DECLARE @WebXConfigurationID INT,
				@stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
				@stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
				@cur_Properties XML;


			PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

			IF (ISNULL(@stored_URL, '')  ISNULL(@cur_URL, ''))
			BEGIN
				PRINT '	- Base URL needs updating'
			END 
			ELSE
			BEGIN
				PRINT '	- Base URL does not need updating'
			END;

			IF (ISNULL(@stored_OpsServiceURL, '')  ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
			BEGIN
				PRINT '	- OPS Service URL needs updating'
				SET @cur_Properties.modify('replace value of (/properties[1] //OPSServiceURL[1] /text())[1]  with sql:variable("@stored_OpsServiceURL")')
			END 
			ELSE
			BEGIN
				PRINT '	- OPS Service URL does not need updating'
			END;
"

We have about 30 SQL instances being backed up by Azure Backup. I have the needed PowerShell commands to *set* the AutoProtect policy, but there doesn't seem to be a command to *get* the AutoProtect status or policy. Anyone got any ideas how to get this via PowerShell? In case anyone is interested, here is the code to *set* the policy: $vault = Get-AzRecoveryServicesVault -ResourceGroupName "MyResourceGroupName" -Name "MyRSVname" $pol = Get-AzRecoveryServicesBackupProtectionPolicy -Name "MyPolicyName" $sqlinstances = Get-AzRecoveryServicesBackupProtectableItem -workloadType MSSQL -ItemType SQLInstance -VaultId $vault.ID foreach ($SQLInstance in $sqlinstances) { Enable-AzRecoveryServicesBackupAutoProtection -InputItem $SQLInstance -BackupManagementType AzureWorkload -WorkloadType MSSQL -Policy $pol -VaultId $vault.ID }

I'm starting with powershell and this time. I have to read all the values from a excel file In this case from a specific sheet. The ultimate goal of this task is to get the values from two columns and then save this values and export to a SQL Table because I have a StoreProcedure to read the table and then create all extended properties needed in one database with thouse values This is the code but I not able to get correctly the data from excel $Excel = New-Object -ComObject Excel.Application $Path = '' #I'm using a shared folder $Workbook = $Excel.Workbooks.Open($Path) $workSheet = $Workbook.Sheets.Item(4) #The number is for because the excel file has 4 sheets #and the sheet desired is the last one $Output = @() $Output = [pscustomobject][ordered]@{ #I think with this I could go through the table but I #would have to calculate the coordinates first. Rol = $Worksheet.Range("A4").text Member = $Worksheet.Range("B4").text } Example of how looks the sheet | Rol | Member | Description | |:---- |:------:| -----:| | Admin | name + email | Description of the rol | | Data Lead | name + email |Description of the rol | | Arquitect | name + email | Description of the rol | | DBA | name + email | Description of the rol | | QA | name + email | Description of the rol | So, my intention is go through the table and get the date as array but to take into account. Sometimes a role can have several members associated with it and that's means more rows in member column Best Regards and thanks in advance

I'm following the official DBATools guide regarding how to query Azure SQL Databases. This code is working: $azureCredential = Get-Credential -Message 'Azure Credential' $azureAccount = Connect-AzAccount -Credential $azureCredential $azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net $azureInstance = "test.database.windows.net" $azureDatabase = "testsource" $server = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -AccessToken $azureToken Invoke-DbaQuery -SqlInstance $server -Query "SELECT @@VERSION" | Format-Table -AutoSize Even if it's always showing the yellow errors: WARNING: Unable to acquire token for tenant 'organizations' with error 'UsernamePasswordCredential authentication failed: AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance. Trace ID: e9cffe3d-xxxx-xxxx-xxxx-310b5365b200 Correlation ID: 99b44f31-xxxx-xxxx-xxxx-dbdfc97482db Timestamp: 2022-09-05 10:36:32Z See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/usernamepasswordcredential/troubleshoot ' and the red error: Connect-AzAccount : UsernamePasswordCredential authentication failed: AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance. Trace ID: e9cffe3d-xxxx-xxxx-xxxx-310b5365b200 Correlation ID: 99b44f31-xxxx-xxxx-xxxx-dbdfc97482db Timestamp: 2022-09-05 10:36:32Z See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/usernamepasswordcredential/troubleshoot At C:\Users\FrancescoMantovani\Desktop\test.ps1:2 char:17 + $azureAccount = Connect-AzAccount -Credential $azureCredential + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Connect-AzAccount], AuthenticationFailedException + FullyQualifiedErrorId : Microsoft.Azure.Commands.Profile.ConnectAzureRmAccountCommand This is the picture: Why is this working even if there is an error? What does this error means and why I'm not kicked out?

I am working on ARM template to setup SQL server VM on Azure with Always on High availibility setup using Win server 2019 and SQL Server 2019 Enterprise image. I am trying to automate everything using ARM template. Now, I need to execute SETSPN commnad on one of the Azure SQL Server VM using AMR template for which I used Script Deployment template and there using **Invoke-AzVMRunCommand** command to run PowerShell script on Azure VM. As Invoke-AzVMRunCommand run PowerShell script with System Account context, I am getting access denied error when SETSPN command try to execute on the remote VM. So, looking for any other option to run PowerShell command to Azure VM in automated way with domain user context having permission to execute SETSPN command. Here is the command I am trying to execute on VM using ARM deployment script and Invoke-AzVMRunCommnad. **setspn -S MSSQLSvc/..com:1433 domain\sqlserviceaccount**

I'm trying to create a SQL script that will create all the stored procedures that were exported from another database. Basically navigating in SSMS to the database > Generate Scripts > select stored procedures > .. I found this query that lists all stored procedures CREATE statements, but the question is how to get all of these rows (result) in a single query/file?

select  mod.definition
from sys.objects obj
join sys.sql_modules mod
     on mod.object_id = obj.object_id
cross apply (select p.name + ' ' + TYPE_NAME(p.user_type_id) + ', ' 
             from sys.parameters p
             where p.object_id = obj.object_id 
                   and p.parameter_id != 0 
             for xml path ('') ) par (parameters)
where obj.type in ('P', 'X')

I tried via a PowerShell script, but the commands are cut short... The PowerShell script:

Param
( 
    [Parameter(Mandatory=$false)][string]$SqlServer = "sql_server_name",
    [Parameter(Mandatory=$False)][string]$SqlDatabase = "db_name",
    [Parameter(Mandatory=$false)][string]$destination = "C:\Users\userx\Desktop\",
    [Parameter(Mandatory=$false)][string]$SqlUser = "sqluser",
    [Parameter(Mandatory=$false)][string]$SqlPassword = "password"
)

function Invoke-Sql
{
    $ErrorActionPreference = "Stop"

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$($SqlServer); Database=$($SqlDatabase); User ID=$($SqlUser); Password=$($SqlPassword)"

    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = Get-Content "$PSScriptRoot\Get_Create_Statement_All_Stored_Procedures.sql" | Out-String
    $sqlCmd.Connection = $sqlConnection

    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.SelectCommand = $sqlCmd
    $dataTable = New-Object System.Data.DataTable
    try
    {
        $sqlConnection.Open()
        $sqlOutput = $sqlAdapter.Fill($dataTable)
        Write-Output -Verbose $sqlOutput
        $sqlConnection.Close()
        $sqlConnection.Dispose()
    }
    catch
    {
        Write-Output -Verbose "Error executing SQL on database [$SqlDatabase] on server [$SqlServer]."
        return $null
    }


    if ($dataTable) { return ,$dataTable } else { return $null }
}

cls

cd $destination

Invoke-Sql | Out-File -FilePath $destination\Create_All_Stored_Procedures.sql

PS. This concerns an Azure SQL database hosted in a Microsoft managed subscription. It seems that using SMO doesn't work on the database as the $smoServer.Databases attribute is empty.

I have only a hostname (what Microsoft calls a COMPUTERNAME). How can I get the names of SQL Server instances running on the host? It appears from other answers that I could look through the services running on the host. However, it is a remote machine and I am not an Administrator for the host or any of the database instances. The organization has not begun to use PowerShell remoting. Is there a better way? How can this be done? Windows PowerShell PS C:\> Get-Service -ComputerName Tyyyyyyy8B Get-Service : Cannot open Service Control Manager on computer 'Tyyyyyyyy8B'. This operation might require other privileges. At line:1 char:1 + Get-Service -ComputerName Tyyyyyyy8B + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Get-Service], InvalidOperationException + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.PowerShell.Commands.GetServiceCommand PS C:\> $PSVersionTable.PSVersion.ToString() 5.1.19041.2364 PowerShell Core PS C:\> Get-Service -ComputerName Tyyyyyyy8B Get-Service: A parameter cannot be found that matches parameter name 'ComputerName'. PS C:\> $PSVersionTable.PSVersion.ToString() 7.3.5 --- > ...why not ask an admin or DBA for help? I am a mere, lowly user. There is significant organizational resistance to making work requests of another group. --- What is the minimum set of permissions I would need to ask for in order to discover SQL Server instances?