SQL

Powershell script to backup all user databases on current SQL server


This script will backup all user databases on the current SQL server. The Input region accepts values for 2 variables:

$Destination ==>This is the folder where backup files will be stored
$Retension ==> Number of days to keep backup files. Backup files older than this will be deleted. If you don’t want any files deleted, make this number high like 99999

The script creates a folder for each database under the $Destination folder. Backup file name contains the database name and date/time of backup. For example, for database ‘INM’, backup file would be ‘H:\SQL-Backup\INM\INM-20160605_081237AM.bak’
Script returns an object collection. Each object has the following properties:

  • ServerName
  • DatabaseName
  • RecoveryModel
  • BackupDateTime(yyyyMMdd_hhmmsstt)
  • BackupDuration(hh:mm:ss)
  • BackupFileSize(MB)
  • BackupFileSize

Sample script output:

SQL02


Also see script to list SQL Recovery Model of all databases on current server, script to modify and list SQL Recovery Model of all databases, and this related post.

Advertisements

SQL backup options and feature details


Simple Recovery Model

Overview:

  • Transaction log is mainly used for crash recovery (no log backup)
  • Transaction log keeps all records after last backup (full or differential)
  • Supports Full and Differential backups only
  • Changes since last backup will be lost
  • Example: Full on Saturdays and differentials on weekdays.

Advantages:

  • Automatically reclaims log space

Dis-advantages:

  • The following features are not supported under the Simple Recovery Model:
    • Log shipping
    • AlwaysOn Database mirroring
    • Point-in-time restores
  • Changes since the most recent backup cannot be recovered

To view a database Recovery Model:
TSQL:

SELECT recovery_model_desc FROM sys.databases WHERE name = ‘MyDBName’

Powershell:

Using the SBSQL module:

Get-SQLRecoveryModel -DBName MyDBName -ServerName MySQLServerName | FT -a

To view Recovery Model of all databases on current SQL server, use the Get-SQLRecoveryModel powershell script.

Sample script output:

SQL01

To change a database Recovery Model:
TSQL:

USE master; ALTER DATABASE MyDBName SET RECOVERY SIMPLE

This will change the Recovery Model for database ‘MyDBName’ to Simple. Valid options are Simple, Full and Bulk_Logged

Powershell:

Using the SBSQL module:

Set-SQLRecoveryModel -DBName MyDBName -RecoveryModel Simple -ServerName MySQLServerName

To modify Recovery Model of all user databases on current SQL server use the Set-SQLRecoveryModel script.


Setting up certificate based communication between SQL server endpoints for SQL mirroring


SQL mirroring is a popular DR option for SQL databases. It provides a warm standby server where a database can be recovered quickly. Although SQL mirroring is being deprecated by Microsoft in SQL 2016 in lieu of Availability Groups, they share several elements of the underlying technologies.

SQL mirroring popularity is often attributed to

  • It does not require shared storage like clustering
  • It does not require a common file share like log-shipping
  • It can be configured for automatic failover (synchronous mode only) with the configuration of a SQL Witness (3rd server). This option requires that the application/client be mirror-aware (include ‘failover partner=xxxx’ in connection string)
  • It can be configured in safety/synchronous mode (default), where a transaction is written to both servers before a commit is returned to the client. This requires low latency between the 2 servers.
  • It can be configured in performance/asynchronous mode, where the primary server sends commit back to client as soon as the transaction is written to the send queue. This may lose data if the primary fails before the transaction makes it to the secondary server redo queue.
  • It can be configured across distant geographical locations (recommend asynchronous mode and certificate based authentication in this scenario)
  • It can be configured between 2 servers that belong to different AD domains using certificate authentication.

This Powershell script automates the last scenario of setting up certificate based communication for a pair SQL 2014 servers in Azure. The script can be slightly modified to work on on-premises SQL servers as well. This script has been tested on SQL 2014 SP1 but should work on SQL 2008 R2 and above.

Region ‘Initialize’ output may look like:

SQL-Mirroring-02

Region ‘configure outbound connections’ output may look like:

SQL-Mirroring-03

Region ‘configure inbound connections’ output may look like:

SQL-Mirroring-04

Finally, region ‘Final Cleanup’ deletes the certificate files and closes open sessions to the 2 SQL servers:

SQL-Mirroring-05

To undo this setup, you can run this TSQL script on the Principal server:

DROP CERTIFICATE Vertitech1SQL2_cert
DROP user Vertitech1SQL2_user
DROP login Vertitech1SQL2_login
DROP endpoint Endpoint_mirroring
DROP CERTIFICATE Vertitech1SQL1_cert

SELECT * FROM sys.sysusers where name = ‘Vertitech1SQL2_user’
SELECT * FROM sys.server_principals where name = ‘Vertitech1SQL2_login’
SELECT * FROM sys.certificates
SELECT name,port FROM sys.tcp_endpoints

and this TSQL script on the Secondary server:

DROP CERTIFICATE Vertitech1SQL1_cert
DROP user Vertitech1SQL1_user
DROP login Vertitech1SQL1_login
DROP endpoint Endpoint_mirroring
DROP CERTIFICATE Vertitech1SQL2_cert

SELECT * FROM sys.sysusers where name = ‘Vertitech1SQL1_user’
SELECT * FROM sys.server_principals where name = ‘Vertitech1SQL1_login’
SELECT * FROM sys.certificates
SELECT name,port FROM sys.tcp_endpoints


Truncating SQL Log files using Powershell


SQLIn active SQL databases, log files may grow to very large sizes and cause performance degradation. The following script truncates SQL log files for all databases on the current SQL server.

The script can be downloaded from the Microsoft Script Center Repository.

This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you’re using an older SQL version, you need to download and install the following 3 components in order:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

I’ve linked to the x64 versions of these files, but you need to get the version that matches your OS.

You also need to remember to run the command:

Import-Module SQLPS

then close ISE and reopen it again. After that you can run the script.


To run this script as a scheduled task:

# To schedule a Powershell script:

$ScriptPath = ‘C:\Scripts\Truncate-SQL.ps1’
$TaskName = ‘TruncateSQL’
$TaskRun = “powershell.exe -NoLogo -NonInteractive -WindowStyle Hidden -Command “”$ScriptPath”””

# Example: Weekly on Sundays at 2 AM
SCHTASKS.EXE /Create /S $Env:COMPUTERNAME /RU SYSTEM /SC WEEKLY /D SUN /TN $TaskName /TR $TaskRun /ST 02:00 /RL HIGHEST /F
break

# Example: Daily at 7 AM
SCHTASKS.EXE /Create /S $Env:COMPUTERNAME /RU SYSTEM /SC DAILY /TN $TaskName /TR $TaskRun /ST 07:00 /RL HIGHEST /F
break

# Run now:
SCHTASKS /Run /TN “\$TaskName”
break

<#
Use
SchTasks.exe /Create /?
To see switch details
#>


Moving SQL Temp database and log files using Powershell


If you need to move the SQL Temp database and log files to a new drive/folder, you need to run a few SQL queries and restart SQL service. The following script automates this task. This can be particularly useful if you need to do this task for many SQL servers.

This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you’re using an older SQL version, you need to download and install the following 3 components in order:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

I’ve linked to the x64 versions of these files, but you need to get the version that matches your OS.

You also need to remember to run the command:

Import-Module SQLPS

then close ISE and reopen it again. After that you can run the script.

You need to edit lines 6 and 7 to enter the new locations of where you’d like to move the Temp DB and Log files. Make sure the necessary folder exists before you run the script (h:\SQL-Temp folder in this example).

Move-SQL-Temp

# Script to move temp DB and its log from one disk location to another
# Sam Boutros
# 6/5/2014
# Move-Temp.ps1
#
$NewTempDBLoc = “h:\SQL-Temp\tempdb.mdf”
$NewTemplogLoc = ‘h:\SQL-Temp\templog.ldf’
# End Data entry section
#
$TempDB = “N'” + $NewTempDBLoc + “‘” # This format is needed for Invoke-SQLCMD
$TempLog = “N'” + $NewTempLogLoc + “‘”
$Loc = Get-Location
$Date = Get-Date -format yyyyMMdd_hhmmsstt
$logfile = $Loc.path + “\Move-Temp_” + $Date + “.txt”
#
function log($string, $color)
{
if ($Color -eq $null) {$color = “white”}
write-host $string -foregroundcolor $color
$temp = “: ” + $string
$string = Get-Date -format “yyyy.MM.dd hh:mm:ss tt”
$string += $temp
$string | out-file -Filepath $logfile -append
}
#
# Import-Module SQLPS # See notes..
log “Modifying Temp DB and Log location..” green
Invoke-SQLCMD -Query “USE [master]”
Invoke-SQLCMD -Query “ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = $TempDB ) ”
Invoke-SQLCMD -Query “ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = $TempLog) ”
Set-Location -Path ($logfile.Split(“:”)[0] + “:”)
log “Restaring SQL Service..” green
Restart-Service MSSQLSERVER -Force