Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation
Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do ...
The problem you are experiencing is with the PowerShell subsystem in SQL Server Agent. It is a bit flakely with using other modules becuase you are put in the context of the SQL Server PowerShell Provider (SQLPS.exe). So it works the same way as if you opened up sqlps.exe and then try to execute your code.
One thing to keep in mind with dbatools module is ...
If you are running this as a SQL Server Agent job step you will need to use the Operating system (CmdExec) step type, not a PowerShell type.
Within the CmdExec it would be best to create a file on the machine with all of the steps that you require and then call that.
For example create a file C:\PowerShellScripts\MyScript.ps1 which contains...
I've tested dbatools' Copy-SqlLinkedServer from SQL Server 2016 to SQL Server 2016 and SQL Server 2014 to SQL Server 2016.
All linked servers and their respective logins were successfully migrated.
I checked both the permissions and ensured the same login was being used. What versions are to you migrating from? What error do you get?
(I am the creator ...
This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.
This script shows the mapping ...
The server object are at the server level e.g. logins, jobs, etc. So you can just use Sync-DbaAvailabilityGroup
Syncs depdendent objects for availability groups. Such objects include:
You need to import the module using import-module dbatools
See Properly importing the module
Specifying the path to the .psd1 file
hope that helps you !
Also, what Scott commented is documented in the same link you have (just read the page carefully to see if you are not missing any steps):
Once you’ve extracted the folder, make sure you rename it ...
You misunderstand how the SQL Agent works. The powershell is run under its own window outside the Instance.
This means until the operation reports back as finished or a failure error occurs, the powershell operation will sit in perpetuaty. If you were to attempt to close the session, it would hang indefinitely until you closed the shell. In a few cases, ...
To expand the reason you can't generally use the PowerShell step with another module is the SQL Server provider. It does not play nice and there is a connect item out to get MS to fix this.
If you add $erroractionpreference = 'stop' to the your job step you will see the full output of why the command is erring:
Executed as user: NT Service\SQLSERVERAGENT....
I think you are using an old version of dbachecks.
Please can you check by comparing the latest version
I think you are hitting this issue
as the value that is coming back for the Backup Date is null. This was fixed ...
I am going to assume that this database (AdminDB) is newly created and newly backed up?
There was an issue created for this as new databases were failing these tests.
A new configuration was created "policy.backup.newdbgraceperiod" (defaulting to value 0 for 0 hours) that excludes new databases ...
One thing you can do is there is an option when you convert from json to actually convert it into a hastable for you...so it can save a few lines of code.
(Get-Content C:\temp\foo.config | ConvertFrom-Json).GetType()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
Well, 4 hours ago self... if you had bothered to inspect the types of $foo and $bar, you might not have wasted a whole afternoon swearing loudly in your open office floor plan as you scroll fruitlessly through the documentation...
~> ($foo | Get-Member).TypeName
~> ($bar | Get-Member).TypeName
While not completely answering your question about moving ALL system databases with Powershell, I did want to provide a working example of moving the individual files for TEMPDB.
Let's assume the current location of your TEMPDB files is
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA
and you want to move them to
The Restore-DbaDatabase in dbatools right now blocks the ability to restore the system databases. It is on the roadmap at some point to work out the process. The block in that command is why Copy-DbaDatabase would fail as well, and you can't move any system database by simple detach/attach method.
Overall it could be worked out to perform the whole task in ...
Is SQL Server installed on this computer? If not, pass in the -NoSqlCheck switch parameter. If you don't use this switch and SQL Server isn't installed, it'll skip over the checks and report this error incorrectly.
Check out this post, do you meet the minimum requirements? Is remoting enabled?
Otherwise run it with the verbose flag and let us know what you get:
Test-DbaDiskAlignment -ComputerName Test_Server -Verbose
Also what version of both the dbatools module and powershell are you using?
Interesting, Doesn't work for me in my lab, but I get a different error. I've been using Powershell in our SQL2014 environment, but I ran into problems, I think related to the PS provider in 2014, and I've been using the cmdexec command to fire up powershell scripts.
My SQL Agent step looks like this in 2016, and it worked: