How to capture errors from SQLCMD

Question: I'm executing code using SQLCMD from a batch file . The code points to a sql file and there is also an output file. SQLCMD -E -S MYSERVER\INST1 -i "setup_job_entry.sql" -o "setup_job_entry.log" But I noticed that if the actual SQLCMD returns an error , for example , if I'm connecting to an server which doesn't exist this error message will appear in the output file - but there will not be an ERROR number , which would allow me to trap and return an appropriate message Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network... Read more →

How to enable MSDTC XA transactions with Powershell

Question: I've read your post on Enable XA transactions for SQL Server , which outlines how to enable XA transactions via the Computer Services module. I'd like to integrate the enabling of XA transactions as part of a scripted SQL Server installation, whoch includes enabling XA transactions for some applications. I'm using Powershell to develop the script . How can I see the current value and also change the value to enable XA ? Answer: It is possible to access and set the MSDTC XA Transactions values using Powershell . You need to make sure your current login has the... Read more →

Powershell Script - Backup SQL databases to a remote drive

Question: Do you have a powershell script to backup SQL databases on a single SQL Server Instance , and direct them to a network share or a local drive? Also , I'd like an email notification with a screen output sent to my email box. The purpose of the script is not to replace the current backup scripting but is for ad-hoc uses Answer: There are plenty of different ways to manage the process of backing up SQL Server databases with Powershell. In this script , the powershell cmdlet Invoke-sqlcmd is used to connect to a sql server , and... Read more →

How to search a different Active Directory Domain with Powershell Get-ADGroupMember

Question: I'm using the Powershell Get-ADGroupMember to iterate through various Groups and return the members. This is working OK - as long as I'm searching in AD groups which are on the same domain server as the server I'm currently logged on. For example - I've logged onto the domain : mydomain1.admin.net . I have authority to check AD groups in this domain. But if I then attempt to do a search on another domain e.g mydomain2.admin.net , even though the domain is in the same AD farm , the Get-ADGroupMember will not find the group. To make things more... Read more →

How create a drive to a network share with Powershell New-PSDrive

Question: I'm trying build some Powershell scripts to automate and standardize the SQL Server patching process. One of the steps is to create a temporary drive with a dedicated letter . This drive should be mapped to a network share. That way the executable , which exists on the network , will run as if on the local server. How can I create the new drive , mapped to a network share in Powershell? Answer: Powershell has a few different cmdlets for this purpose. I like using New-PSDrive . The New-PSDRive allows you to nominate a drive letter , point... Read more →