Backup and Restoring SQL DBs via PowerShell

Occasionally you may need to create a backup of a SQL Database outside of the normal backup schedule.  For instance, a test environment where you need to restore back to a known state in the future, or if the DB Log Files are growing out of control and you need to shrink them, etc.  This post will cover how to create a database backup using PowerShell cmdlets included with SQL Server 2012 and later releases.

SQL PowerShell Module Basics

First the basics:  When working with the SQL PowerShell module you can either open it via the Start Menu Shortcut, the links within SQL Server Management Studio (SSMS) or by simply importing the module with the following command:

Import-Module SQLPS -DisableNameChecking

Once imported, it will change the current working directory to the SQL Server, where you can explore everything with standard dir and cd commands. For instance you would view the databases by going to:

cd .\sql\servername\instancename\databases

Where servername is the local SQL Servername and instancename is the instance name, which is usually “default” if you don’t use named SQL instances.

Backing up a SQL Database

Now, once you are in the databases “folder”, you can backup a database by using something similar to:

Backup-SqlDatabase -Database DatabaseName -BackupAction Database -BackupFile "U:\MSSQL\SQL_BACKUP\Databasename-psbackup.bak" -Compression On -Initialize

Where DatabaseName is the name of the DB you want to backup, U:\MSSQL\.... is the location of where the backup should be located. Note that normally this needs to be on a local disk of the SQL Server.

Restoring a SQL Database

Now, if for some reason you need to restore a DB from a backup, go back into the “database” folder, then execute something similar to:

Restore-SqlDatabase -Database DatabaseName -BackupFile "U:\MSSQL\SQL_BACKUP\Databasename-psbackup.bak" -ReplaceDatabase

Again, where DatabaseName is the name of the DB you want to restore and U:\MSSQL\.... is the location of the backup file to use. Note that this command will drop all connections to the database and restore the data from the backup.

Sending Anonymous Email via .net

Microsoft’s .net mail libraries by default will use the NTAuth credentials of the user performing the action. Many Exchange and other mail servers that are configured to be more restrictive may reject sending emails via the .net library using the defaults. This post will show how to configure your application or code to use the Anonymous Logon account instead of the logged on user’s account.

An example error is below:

Exception in sending email: Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender
at System.Net.Mail.MailCommand.CheckResponse(SmtpStatusCode statusCode, String response)
at System.Net.Mail.MailCommand.Send(SmtpConnection conn, Byte[] command, String from)
at System.Net.Mail.SmtpTransport.SendMail(MailAddress sender, MailAddressCollection recipients, String deliveryNotify, SmtpFailedRecipientException& exception)
at System.Net.Mail.SmtpClient.Send(MailMessage message)
at SendEmail.Send(SendEmail* )

To overcome this issue in a C Sharp application, adjust the initiated SmtpClient to not use the default credentials, such as:

using System.Net.Mail;
...
MailMessage mail = new MailMessage("support@appadmintools.com", "sales@appadmintools.com");
SmtpClient client = new SmtpClient();
client.Port = 25;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Host = "mail.appadmintools.com";
mail.Subject = "Test Mail from C Sharp.";
mail.Body = "This is the mail body";
client.Send(mail);

Via PowerShell, you should create a new PSCredential and use that with the Send-MailMessage Cmdlet, for instance:

$s = New-Object System.Security.SecureString
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "NT AUTHORITY\ANONYMOUS LOGON", $S
Send-MailMessage -From $fromaddress -To $mailtoaddress -Subject $messageheader -Body $messagebody -Attachments $attachments -Credential $creds

If you have an application that allows you to specify an account to use for SMTP communication, simply set it to use “NT Authority\Anonymous Logon” account, similar to the image below taken from System Center Orchestrator 2012R2.

System Center Orchestrator 2012R2 Anonymous Email settings

Bottom line, if you are having issues sending emails and you confirmed all the settings, try to capture the network traffic you are using (default smtp port is 25), which should give you the error that is preventing the sending of emails.