Powershell / Use PowerShell to Backup all user databases to Azure Storage

Powershell / Use PowerShell to Backup all user databases to Azure Storage
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 Summary: Backup all user database from specified SQL Server instance to the buffer location, and then copy from the buffer location to the azure storage, and create log file with results. Description: This PowerShell script can be invoked remotely from another PC trough the command line, with PowerShell or executed remotely through task scheduler adding parameters – instance name, backup...
read more

SQL Server / Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000]

SQL Server / Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000]
Multi Server Administration MSX/TSX. When I have multiple instances with same or similar jobs I usualy use Multi Server Administration to create the job just once and then add target servers for that job. I just wanted to point out some errors you can face during Multi Server Administration implementation. Message [298] SQLServer Error: 18456, Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000] What does this mean. Your SQL Server Agent probably using NETWORK SERVICE credential for log on account (default NT...
read more

Powershell / Providers / Get-ChildItem : Invalid Path

Powershell / Providers / Get-ChildItem : Invalid Path
Using UNC paths when working with PowerShell providers. There are errors you can encounter using UNC paths if your current provider is different then filesystem. PowerShell will just use the current one. We can use the Get-PSProvider cmdlet to get full list of the PSProviders available on our system. My current one is filesystem.   So lets test this. I will use one simple PowerShell script to delete all sub-folders and files from network folder. $FolderNetworkPath="\\10.0.0.102\test\" Write-Output ((Get-Date -Format g) + " - Delete Files...
read more

Powershell / Credentials / Connect to SQL Server with PowerShell

Powershell / Credentials / Connect to SQL Server with PowerShell
How to login using SQL Server Management Object (SMO)   For windows authentication #################################################################### ####################### Windows Authentication ##################### #################################################################### [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "InstanceName" $s.ConnectionContext.LoginSecure=$true $s.Databases | select name, size, status...
read more

Azure / SQL Server / Azure DB / Add Logins, Users, Permissions

Azure / SQL Server / Azure DB / Add Logins, Users, Permissions
After we created our SQL Server and Azure DB now we need to connect to our Azure DB using SSMS. First of all, we need to add firewall rule to be able to access our Azure SQL Server from our location. Click on yours Azure SQL Server on Azure portal, and choose Firewall / Virtual Networks.   Then just add your firewall rule and IP Address, or IP range if you want. After firewall rules have been added, users can connect to a Azure SQL Database using administrator accounts, or as an database owner or database users (If they already exist)....
read more

Azure / SQL Server / Create DB on Azure

Azure / SQL Server / Create DB on Azure
First of all, we need to login to Azure Portal and to create Logical SQL Server on Azure   Next, we need to put the name of the server, as well as server admin login account for this Azure Logical SQL Server. After that, we can create a new one or to use existing resource group.   When we finish this part of configuration we can start with creating Azure SQL Server Database. We need to define database name, and subscription we want to use for this SQL Database   After that, we need to select source of our database (blank,...
read more

Azure / SQL Server / Cannot open user default database (Login failed)

Azure / SQL Server / Cannot open user default database (Login failed)
You want to create a new login on Azure Logical SQL Server using your Azure Admin account. To do that you must be connected to the master database on SQL Azure with the administrative login. CREATE LOGIN [azure_db_user] WITH PASSWORD=N'password' GO   After that, you need to create new DB user for that login (db_owner in this example). To be able to do that you must be connected as an admin to the database where you want to create that user. CREATE USER azure_db_user FOR LOGIN azure_db_user GO EXEC sp_addrolemember N'db_owner',...
read more

SQL Server / DB Backup Size Last Month / Email Alert

SQL Server / DB Backup Size Last Month / Email Alert
This is one simple example how you can use sp_send_dbmail stored procedure from msdb database to send DB backup size daily. I used folowing arguments to achive this: @profile_name (name of an existing Database Mail profile, type sysname, default NULL), @recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)), @body (Is the body of the e-mail message, nvarchar(max)), @subject (Is the subject of the e-mail message, nvarchar(255)), @body_format (Is the format of the message body, varchar(20)) More...
read more

Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)

Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
“Operating system error 32 (The process cannot access the file because it is being used by another process)” message in SQL Server error log BackupDiskFile::OpenMedia: Backup device ‘\\NAS\Shared_LogShip\DB_log.trn’ failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.). If you are using shared NAS for your logs backup location for the primary server and you restoring those logs directly to the secondary server (without copy job) you can face this kind...
read more

SQL Server / Upgrading the Secondary Server Instances (STANDBY)

SQL Server / Upgrading the Secondary Server Instances (STANDBY)
If you planning to upgrade to the new SQL Server Version 2016 or 2017 and if there is log shipping in place between those servers you should follow some upgrading order. Always upgrade the secondary SQL Server instances first, before upgrading the primary server instance. If you do the opposite log shipping will fail because a log backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server. But this is not a reason why I am writing this post. I am writing to alert you that in case you have a secondary...
read more

« Previous Entries Next Entries »