Currently Browsing: Powershell

SQL Server / PowerShell / Check SQL Server version and a current patch level for all servers you specify using PowerShell

Posted by in Azure, Powershell, SQL Server on Apr 24th, 2022 | 0 comments
SQL Server / PowerShell / Check SQL Server version and a current patch level for all servers you specify using PowerShell
Script Download: SQLServerUpdateStatus Download Summary: Check SQL Server Version and the current patch level for all servers you specified. As well check the latest patches/updates available for the installed SQL Server version and send an email with results. Description: SQL Server Instance Update Status PowerShell script which can be invoked remotely from another PC through the command line, with PowerShell or executed remotely through task scheduler adding servers names. The script is checking server registry values for (Version, PatchLevel, Edition, SQLPath), you can choose and add other values...
read more

Azure / PowerShell / Deploy Azure SQL database DR environment with integrated azure SQL Analytics Solution using PowerShell

Posted by in Azure, Powershell, SQL Server on Mar 6th, 2021 | 0 comments
Azure / PowerShell / Deploy Azure SQL database DR environment with integrated azure SQL Analytics Solution using PowerShell
Script Download: AZ_Module_Deploy_Azure_SQL_Database_DR_Environment_With_AzureSQL_Analytics_Solution_Blog-1Download Summary: Use PowerShell to create cloud DR environment using Azure SQL Databases (PaaS) with integrated azure SQL Analytics Solution and send Email Report with the connection string details. Description: Use PowerShell to deploy cloud service with Azure SQL Database (PaaS) with good resilience to outages (GEO Replications, Failover Groups) and with integrated azure SQL Analytics Solution. PowerShell: The script will include the following: Connect to Azure Account;Use existing or create...
read more

SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell

Posted by in Powershell, SQL Server, SQL Tips and Tricks on Jan 28th, 2020 | 0 comments
SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell
Export SQL Data to multiple CSV files. There are many different ways to do this (SSIS, TSQL) but I think that PowerShell could be really efficient tool for such small random tasks. For this example I will use test table with 17220 rows. First step is to export whole table to CSV file using export-csv cmdlet.  Export-CSV converts objects into a series of comma-separated value (CSV) strings and saves the strings to a file. If you want to find more details about Export-CSV cmdlet and see more usage examples you can read this MS article. param ( [String] $destination = 'your_destination', [String]...
read more

PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs

Posted by in Powershell, SQL Server, SQL Tips and Tricks on Feb 12th, 2019 | 0 comments
PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs
Restore multiple TRN logs using PowerShell. In this example I will show you really simple way how you can use PowerShell to restore multiple logs at once. After restoring full backup WITH NORECOVERY you need to figure it out last log sequence number of the last transaction in the backup set. TRN Log backups must be restored in the order in which they were created. Log backups contain log sequence numbers (LSN), and they must be restored in that particular order. There are few ways to find last LSN. USE msdb GO SELECT TOP 10 bck.database_name , bck.first_lsn , bck.last_lsn , bck.backup_start_date ,...
read more

Azure / PowerShell / Deploy Azure SQL database DR environment (PaaS) using PowerShell

Posted by in Azure, Powershell on Jan 21st, 2019 | 0 comments
Azure / PowerShell / Deploy Azure SQL database DR environment (PaaS) using PowerShell
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Deploy-Azure-SQL-DR-47dfa7f7 Summary: Use PowerShell to create cloud DR environment using Azure SQL Databases (PaaS) and send Email Report with the connection string details   Description: Use PowerShell to deploy cloud service with Azure SQL Database (PaaS) with good resilience to outages (GEO Replications, Failover Groups) PowerShell: Connect to Azure Account, Use existing or create new Resource Group, Create primary Azure Logical SQL Server, Set an admin login and password for...
read more

Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs

Posted by in Powershell, SQL Server, SQL Tips and Tricks on Dec 5th, 2018 | 0 comments
Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs
If you are using Azure SQL Database with geo-replication or auto failover group and if you are using logins and users rather than contained users you can end up with the following error when you try to connect to the secondary/failover database after failover occured.   If you already created a login on the failover server using SQL command -- ====================================================================================== -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database --...
read more

Azure / SQL Server / This location is not available for subscription

Posted by in Azure, Powershell, SQL Tips and Tricks on Oct 22nd, 2018 | 5 comments
Azure / SQL Server / This location is not available for subscription
During deployment of Azure SQL Server in Canada East location (new Azure Subscription) I got the following error: Using Azure Portal   Using Powershell   At the same time if I list all available azure location for the current subscription I can see that Canada East is there Get-AzureRmLocation | select displayname   The reason is that Canada East region is not activated for my new Azure Subscription. To resolve this issue just contact the Azure support to have this enabled for you. You can do this for free using support page on your Azure Portal.   Open a new support request  ...
read more

PowerShell / SQL Server / ConnectionContext.StatementTimeout

Posted by in Powershell, SQL Server on Sep 30th, 2018 | 0 comments
PowerShell / SQL Server / ConnectionContext.StatementTimeout
Recently I had an issue with a database backup PowerShell script one of our customer using. The script was working fine until trying to do a backup of the large database. During backup attempts script starts failing with the following error: Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'ServerName'. " At line:67 char:1 + $dbBackup.SqlBackup($s) + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FailedOperationException Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server...
read more

PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server

Posted by in Powershell, SQL Server on Aug 8th, 2018 | 7 comments
PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server
Some users complained that they cannot use my PowerShell script published on TechNet https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 It is a PowerShell script to Backup all user databases to Azure Storage. They experienced the following error: 8/14/2018 11:18 AM – Setting Folders PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.” new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is...
read more

Powershell / Azure Automation / Delete older Containers from Azure Storage Account

Posted by in Azure, Powershell on Jul 4th, 2018 | 0 comments
Powershell / Azure Automation / Delete older Containers from Azure Storage Account
This will be a simple example of how you can delete older containers in the Azure storage account. I will use Azure automation to scan storage containers and delete those matching the certain date pattern. First, you need to choose an existing one or create new automation account to run your Runbooks   You should put the script within your Runbooks under the process automation. You will have some useful tutorial Runbooks as well.   We will use connection name with information needed to authenticate with Azure so that we can manage Azure classic resources from...
read more

« Previous Entries