dataplat/dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer

dbatools's Issues

All commands that use WMI: Add -Credential support for WMI

Sometimes it would be -Credential, but if it also connects to SQL Server (like in Get-DbaTcpPort) then that would be -SqlCredential and -WinCredential?

Test-SqlTempDbConfiguration - tempdb on C: validation

This is not working as expected.
Was running a test on a server with only C drive and the test was false. Should be true.
After debug I saw that we are using:
($datafiles.rows | Where-Object { $.FileName -like 'C:*' }).Rows.Count
but should be:
@($datafiles.rows | Where-Object { $.FileName -like 'C:*' }).Count

@MikeFal want to make the correction? Or can I make it?

Copy-SqlLogin issues

• Detect schema ownership prior to user drop attempt
• Will not drop login in destination if permissions are set
• Test task

ReuseFolderStructure sux

Restore-HallengrenBackup error, not sure why

Hi.
First off, great work, thank you for this set of scripts. They look neat.

Now, I noticed that the detach/attach method does not attach again in the old server. And then I had permission troubles attaching again ( ?!?! )
So I was testing the restore from backup method, so I can maintain source server working with ease, if needed.

My backups are made using Ola's set of scripts. So I tested the restore-hallengrenBackup

My the error is:

File list could not be determined. This is likely due to connectivity issues or tiemouts with the SQL Server, the
database version is incorrect, or the SQL Server service account does not have access to the file share. Script
terminating.
At C:\Users\Administrator\Documents\WindowsPowerShell\Modules\dbatools\Functions\Restore-HallengrenBackups.ps1:206
char:12
+             catch { throw "File list could not be determined. This is likely due to conne ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (File list could...pt terminating.:String) [], RuntimeException
    + FullyQualifiedErrorId : File list could not be determined. This is likely due to connectivity issues or tiemouts
    with the SQL Server, the database version is incorrect, or the SQL Server service account does not have access to
   the file share. Script terminating.

Could it be that -Path doesn't work as a NetworkPath?
Maybe I need to copy the backups to this machine? If so, could you add -NetworkPath parameter?

copy-sqldatabase, no exclude parameter

In the comments and help both show a exclude command to limit which DBs are copied. However when running the function the exclude command is not a valid parameter.

Copy-SqlDatabaseMail - Service Broker Activation

activate service broker in msdb if necessary

SMO not loading in some environments

Set-DbaDatabaseOwner additional checks

Set-DbaDatabaseOwner needs additional checks for Offline and ReadOnly status, currently throws error and stops cmdlet if the database is in those states. Also check for new owner already being mapped to the db, this also throws an error and stops cmdlet.

Copy-SqlJob may be duplicating actions

During testing for PR #133 I noticed the -Whatif output seemed to be duplicating.

What if: Performing the operation "Creating Job Some Report Job" on target "somesqlserver".
What if: Performing the operation "Creating Job Some Report Job" on target "somesqlserver".
What if: Performing the operation "Showing finished message" on target "console".

Without the WhatIf (still including -Verbose, easier to see when you remove the Write-Verbose $sql line, otherwise verbose outputs all the SQL used):

VERBOSE: Performing the operation "Creating Job Some Report Job" on target "somesqlserver".
Copying Job Some Report Job
VERBOSE: Performing the operation "Creating Job Some Report Job" on target "somesqlserver".
Disabling Some Report Job on somesqlserver
VERBOSE: Performing the operation "Showing finished message" on target "console".
Job migration finished

Expand-SqlTlogResponsibly backups when -WhatIf is declared

If I run this command with the -WhatIf switch to validate what it's going to do, it backs up anyway. This could be problematic and generate uneeded backups, along with possibly disrupting the log backup chain. I suggest that this get altered that if -WhatIf is declared it will provide a message that a log backup will be taken, but not execute.

Copy-SqlDatabaseMail - send test email to confirm it is working after migration

(js0505)

I would suggest sending 2 test emails, one from DB_mail to confirm server can send email and then one from SQL Agent to ensure notifications are set correctly upon failure.

WhatIf and Confirm parameters

The standard (and easiest) way to add WhatIf and Confirm parameters is to use the SupportsShouldProcess parameter of the CmdletBinding attribute. It adds the code/help automatically for you.

Otherwise, you need to add both manually, so that's a related doc issue.
https://trello.com/c/ZcZgUk12/292-help-for-whatif-confirm

sugestion: detatch/attach should re-attach files back on source server

Just because we migrate everything from one server to another, doesn't mean the source server should be left with no databases.
As such the scripts should leave the source server ready to run after migration.

Export-SqlLogin - Errors in HELP Part and Website - ParameterName wrong

Hi,
today I've tried to export a single SQLServerLogin by using the example from the website (similiar to HELP Part)
Export-SqlLogin -SqlServer SQLServerName -Login testsqlserveruser -FileName C:\temp\login_export.sql

But that won't work on my Windows 2008R2 + SQLServer 2012 EE
A little Research showed me that the Parameter is FilePath instead of FileName and on my machine i got the error that it can't resolve the parameter binding with its position....

The only way it worked for me on my machine was
Export-SqlLogin -SqlServer Servername\InstanceName -Login testsqlserveruser --FileName C:\temp\login_export.sql

And it would be very nice if you could make it a little bit clearer what -SqlServer means... only the servername or only instance... ok with a little bit of thinking you can resolve it by yourself but a hint could make it a little bit easier.

Instead of:
.PARAMETER SqlServer
The SQL Server to export the logins from. SQL Server 2000 and above supported.

it would nicer (e.g.)
.PARAMETER SqlServer
Complete SQL Servername with InstanceName without quotes. SQL Server 2000 and above supported.

Remove-SqlDatabaseSafely - SQL Agent check

The check isn't accurate. Copy-SqlAgent stuff has a more accurate check. In this case, Agent was running but couldn't be verified, and the script attempted to start Agent. In SSMS, the circle was white. Probably related.

Event notifications and related permissions disappear

Found that event notifications can get lost when you convert. Found that you have to re-apply connect permissions. Note, ensure broker got copied over, I have seen where it has not.

(Chrissy LeMaire)

@js0505 You are a gold mine! Thank you. Can you give me steps to reproduce? Some T-SQL from start to finish would be great.

(js0505)

During my next migration from a system that contains EN I will work on generating the script for you. We are doing migrations all month so hopefully next week.

(Chrissy LeMaire)

@js0505 when you say " ensure broker got copied over", is that the Broker Enabled or something else? I don't use Event Notifications, but they look like they are scoped at the DB. Is this accurate? Are they usually copied properly during backup/restore?

(js0505)

Broker Enabled, and yes "usually" everything gets copied over, however, there are times I have to GRANT CONNECT ON::ServiceBroker TO ACCOUNT on the endpoint server.

BackupRestore without SetSourceReadOnly

I cannot find a way to perform Start-SqlMigration with the -BackupRestore method without it trying to set the source databases to read only. I have tried not specifying -SetSourceReadOnly and I have tried -SetSourceReadOnly:$false. How do you perform a SqlMigration without it modifying anything on the source server?

Thanks!

Restore-HallengrenBackups.ps1 - Test-SQLSA Function - Sysadmin check fails if User is a member of an AD Group with SysAdmin permissions

If the user running the script is a SysAdmin via an AD Group, the function will report they do not have permissions to run the restore process.

I'm looking into options to check User is a member any AD Groups with SysAdmin rights

BatchParser error in ExportImportSqlConfig.ps1

Migrating SQL Server Configuration
Start-SqlMigration : Configuration migration reported the following error Exception calling "ExecuteNonQuery" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."

Using ReuseFolderStructure causes errors while migrating Database

Specifications

Source: Windows Server 2003
SQL Server Version: 2005

Destination: Windows Server 2008r2
SQL Server Version: 2008

Command: Copy-SqlDatabase -Source SourceName -Destination DestinationName -Verbose -BackupRestore -ReuseFolderstructure -NetworkShare \\fileshare\sql\migration -All -Force

Expected behavior

Database migration should complete

Actual Behavior

Shows whole bunch of errors. Like:

(1) Join-AdminUnc : Cannot process argument transformation on parameter 'filepath'. Cannot convert value to type System.String.
At C:\dbatools\Function\Copy-SqlDatabase.ps1:650 char:45...

(2) WARNING: Restore failed: Restore failed for Server 'Destination'.

(3) Copy-SqlDatabase : Failed to update DatabaseOwnershipChaining for False on something on Destination
At C:\dbatools\Functions\Copy-SqlDatabase.ps1:1039 char:3...

(4) Copy-SqlDatabase : Failed to update Trustworthy to False for something on Destination
At C:\dbatools\Functions\Copy-SqlDatabase.ps1:1039 char:3...

Workaround

Not using -ReuseFolderstructure seems to migrate database properly.

Copy-SqlJob does not copy maintenance plans

If you are dealing with maintenance plans (not custom SSIS packages) for a given instance the cmdlet is only copying the job definition, not the actual maintenance plan.

Do we want cmdlet for copying maintenance plans or include T-SQL to copy msdb.dbo.sysssispackages (just maintenance plans) to destination. Other option would be to look at using SMO for SSIS to export the packages, but T-SQL likely going to be much less painful. Possibly this code: http://dba.stackexchange.com/a/112434/507

Get-SqlRegisteredServername - Return NetBIOS names

Could be a switch such as -HostNames or -NetBIOS etc. Will allow piping to cmdlets which don't take SQL connection strings such as Get-DiskSpace or Test-SqlDiskAllocation.

Find-DuplicateIndexes not outputing all of the results in file

Find-DuplicateIndexes - not outputting all of the results in file, only outputs first record for each database that it finds duplicate for. Gridview shows all the results correctly.

(Cláudio Silva)

The main goal is output the drop statements for the selected rows.
Isn't this what is happening?

(Chrissy LeMaire)

That is my understanding as well. @js0505 did you select the ones you wanted to output the T-SQL for?
Can you paste the full command you used?

(js0505)

Yes, I select the ones I wanted for output, however, when there is more than one (Well technically 2 because of the duplicate) in the list per database, it will only output the first click to the file. Command was Find-SqlDuplicateIndex -SqlServer ServerA

Get-DbaDiskSpace - Output format issue

Get-DbaDiskSpace - Output format might give an error like "Cannot convert value "1.010.880,00" to type "System.Int64"" when try to set the value to an int/long variable. It is not really a bug is an improvement that can be made to the digit symbol group not be returned.

For example: 1.010.880,00 should be returned to: 1010880,00

Here I'm trying to set the value for an [long]$variable the FreeInKB from the Get-DbaDiskSpace.

Obviously I can replace the '.' or ',' for '' and work, but I think once this command can and should be reused should be more generic.
Thoughts?

Copy-SqlLogin should warn or not copy SQL Logins if the destination doesn't have mixed mode enabled

Question raised during presentation at SQL Saturday, this cmdlet should probably warn or just not copy over SQL Logins if mixed mode authentication is not enabled on the target.

Break down what perms are needed per command

Break down what perms are needed per command, then we can add support for those roles/needs in Connect-SqlServer.

Right now, I have -RegularUser which simply doesn't check for role membership, but that's all.

Not really an issue but suggestion

Can I suggest adding the option to backup to multiple files for larger databases? I have seen a 50 percent increase in performance on backups and restores.

Can't count number of passed $sqlserver params when piped using $sqlserver.count

Instead, do $servercount = ++$i inside PROCESS then implement it like so

if ($servercount -eq 1)
{
    throw $_
}
else
{
    Write-Warning "Can't connect to $servername. Moving on."
    Continue
}

Copy-SqlJob does not include job name for ones that are skipped

When a job is skipped it provides the message:
Write-Warning "Database(s) $missingdb doesn't exist on destination. Skipping."

This should include the $jobname so what job was actually skipped can be easily identified.

Copy-SqlAgentJob - Allow the ability to exclude/incldue jobs based on partial name

Copy-SqlAgentJob - Allow the ability to exclude/include jobs based on partial name. (I.e. All jobs that start with Admin)

Start-SqlMigration error

Running Start-SqlMigration on local machine with SQL Server 2014 --> SQL Server 2016. I get an error around the linked server migration that references "unknown key size". The script continues like it is working, even showing messages like it is...but it basically stopped and quit as the error message shows. The process did not do any hard stop (if it should have).

Pastebin of output log.

Issue with named instances

Hi,

I am having issues with running migrations on from/to named instances.

For example, if I run:

Copy-SqlLinkedServer -Source server\instance -Destination server\instance2

I get the following error:
WARNING: could not be added to server\instance
WARNING: Reason: System.Management.Automation.RuntimeException: The regular expression pattern 'server\instance' is not valid. ---> System.ArgumentException: parsing "'server\instance'" - Unrecognized escape sequence \i.
at System.Text.RegularExpressions.RegexParser.ScanCharEscape()
at System.Text.RegularExpressions.RegexParser.ScanBasicBackslash()
at System.Text.RegularExpressions.RegexParser.ScanRegex()
at System.Text.RegularExpressions.RegexParser.Parse(String re, RegexOptions op)
at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options, TimeSpan matchTimeout, Boolean useCache)
at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options)
at System.Management.Automation.ParserOps.NewRegex(String patternString, RegexOptions options)
at System.Management.Automation.ParserOps.ReplaceOperator(ExecutionContext context, IScriptExtent errorPosition, Object lval, Object rval, Boolean ignoreCase)
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )

Copy-SqlJob does not -DisableOnDestination

I ran Copy-SqlJob on an array of jobnames with -DisableOnDestination. When I reviewed the final product, only the first two jobs in the array got disabled. Everything else was enabled.

Detect server version before attempting CMS migration

Central Management Server is only supported in SQL Server 2008 and above.

Log shipping for migrations

????

Local <> Local Server

The local <> local server check has bad logic. Expects to always migrate remotely.

General - Do not strip FQDN

If someone specifies an FQDN, reattach the domain back to the server name when testing for netbios/ip/etc. $server.ComputerNamePhysicalNetBIOS + $domain. This impacts many commands, including Remove-SqlDatabaseSafely which broke for me because the server's dns wasn't in my search suffix.

Mirroring for migrations

Fix certificate permissions

(js0505)

had to add the following: Use db

GRANT ALTER ANY SYMMETRIC KEY TO xxx
GRANT VIEW DEFINITION ON CERTIFICATE::cert TO user
GRANT CONTROL ON CERTIFICATE::cert TO user

(js0505)

Certificate permissions did not get moved during migration hence reason for the card.

(Chrissy LeMaire)

@js0505 exactly what I need, thank you!

(js0505)

I have to adjust the script because I already new the certificate name. It is not cert as stated in the script below as well as the user.

(js0505)

also:

select name
from sys.databases
where is_master_key_encrypted_by_server = 1
use <database>
go
open master key decryption by password = '<Password>'
alter master key add encryption by service master key
go

Reset-SqlSaPassword sometimes falsely reports that account does not exist

Solve by using -Force to skip check or just show a warning and continue.

Move-SqlDatabaseFile commited to development branch needs testing

Review #175 and test appropriately, probably just focus on any files changed which are shared etc.

Restore-HallengrenBackup doesn't work with Path character "$"

Hi,

The Restore-HallengrenBackup function fails when trying to restore databases from a file share with character "$" in it's path.

When using Ola Hallengren's backup script to backup databases in an Availability Group, the backup files are stored in a folder with naming format "cluster_name$availability_group_name". The files are also named using naming format "cluster_name$availability_group_name_databasename_FULL_COPY_ONLY_20160627_213000.bak"

Basically, I receive the following error:
File list could not be determined. This is likely due to connectivity issues or tiemouts with the SQL Server, the database version is incorrect, or the SQL Server service account does not have access to the file share. Script terminating.

There's no issue with SQL server timeouts or service accounts access rights.

When I create a different folder (as a test) without a $ character in the same fileshare, copy a few database backups and remove the "$" in the copied backup files and point the Restore-HallengrenBackup function to this test folder on the fileshare restores work flawlessly.

Basically, the "$" character that Hallengren's backup script adds in the backup file path as a replacement of "" breaks your restore script. Is there a way to mitigate this issue?

Copy-SqlLogin should not require sysadmin privileges

Our PS/Sysadmin commented out some of the root-level checks to remove the "sysadmin" requirement. However, this should probably be changed to something a bit more appropriate to check the actual permissions required (securityadmin?).

We were able to run the copy-sqllogin function with the source user set as a security admin.

Show-SqlServerFiles and root dirs

Show-SqlFileSystem when choosing the root of directory should be reported C:\ and not only C:

Copy-SqlLinkedServer ignored Linked server

I ran Copy-SqlLinkedServer in both -WhatIf and not whatif mode and it ignored a linked server that was 2 characters long, "SQ".

Here is the script for creating the Linked Server for more information so you can try to diagnose what went wrong:

USE [master]
GO

/****** Object: LinkedServer [SQ] Script Date: 1/11/2016 3:23:27 PM *****/
EXEC master.dbo.sp_addlinkedserver @server = N'SQ', @srvproduct=N'Cache ODBC', @Provider=N'MSDASQL.1', @datasrc=N'LABSQ'
/ For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQ',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'lazy schema validation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQ', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

Copy-LinkedServer - option to automatically upgrade the connection strings of linked servers

Copy-LinkedServer - option to automatically upgrade the connection strings of linked servers where the SQL Native Client is used/configured.

(Andreas Häusler)

for example: sqlncli10 -> sqlncli11 when migrating 2012 to 2014.

Invoke-DbaManagedComputer requires additional testing

This cmdlet has been commited to the development branch by @ctrlbold to facilitate new cmdlets being created by contributors. Local testing was performed by the author. Original PR is #170.

-DisableOnDestination does not appear to work in Copy-SqlJob

During testing for PR #116 I noticed -DisableOnDestination did not seem to be working for me, requires further testing and possibly checking of -DisableOnSource too.

Detect Fatal Backup/Restore messages, and error out.

Right now, backup/restore keeps trying and continues on through each specified database, even when some errors are encountered. That's acceptable in some instances, but in others, it is not.

An example of a non-recoverable error would be: Cannot open backup device xyz. Operating system error 52(You were not connected because a duplicate name exists on the network. Go to System in Control Panel to change the computer name and try again.).