Setting Up and Using Azure VM SQL Automated Backup (and Restore)

Posted on July 24, 2015 by RobSewell

This weekend I was creating some Azure VMs to test and was required to use the GUI for some screenshots. I have always used my Powershell scripts described here to create my test systems and with a new job taking up a lot of my time had missed the announcement about Azure SQL Automated Backup and Azure SQL Automated Patching so was surprised to see this screen

I read the announcement and also the details on MSDN https://msdn.microsoft.com/en-us/library/azure/dn906091.aspx which show that this requires the SQL Server IaaS Agent. This is a default option on new virtual machines.

There are some other considerations too. It is only supported for SQL Server 2014 and Windows Server 2012 and 2012R2 at present and you can set a retention period to a maximum of 30 days but it is automated. You do not have to decide upon the backup strategy Azure will decide the frequency and type of backups dependent upon the workload of the database and some other factors such as

A full backup is taken
○ when an instance is added to use Managed backup
○ When transaction log growth is 1Gb or more
○ At least once a week
○ If the log chain is broken
○ When a database is created

A transaction log backup is taken
– If no log backup is found
– Transaction log space used is 5Mb or larger
– At least once every two hours
– Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.

From <https://msdn.microsoft.com/en-gb/library/dn449496(v=sql.120).aspx>

There are some restrictions
– Only database backups are supported
– System databases are not supported so you need to back those up yourself
– You can only back up to Azure storage
– Maximum backup size is 1Tb as this is the maximum size for a blob in Azure storage
– Simple recovery is not supported
– Maximum retention is 30 days – if you are required to keep your backups for longer than 30 days for regulatory or other reasons you could simply use Azure Automation to copy the files to another storage account in Azure)

How to set it up.

If you are using the GUI then you will find SQL Automated Backup in the optional config blade of the set up. You can follow the steps here to set it up. If (like me) you want to use Powershell then use the following code after you have created your Virtual Machine

$storageaccount = "<storageaccountname>"
$storageaccountkey = (Get-AzureStorageKey -StorageAccountName $storageaccount).Primary
$storagecontext = New-AzureStorageContext -StorageAccountName $storageaccount -StorageAccountKey $storageaccountkey

$encryptionpassword = (Get-Credential -message 'Backup Encryption Password' -User 'IGNOREUSER').password
$autobackupconfig = New-AzureVMSqlServerAutoBackupConfig -StorageContext $storagecontext -Enable -RetentionPeriod 10 -EnableEncryption -CertificatePassword $encryptionpassword
Get-AzureVM -ServiceName <vmservicename> -Name <vmname> | Set-AzureVMSqlServerExtension -AutoBackupSettings $autobackupconfig | Update-AzureVM

Once you have run the code, Azure will take care of the rest. Add a couple of databases to your instance and look in the storage account and you will see this

And in the automaticbackup container you will find the Certificates and master key backups

It will also create a credential

You can use the same credential to back up your system databases. If like me you use Ola Hallengrens excellent Maintenance Solution then simply change your systems backup job as follows


USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name = 'DatabaseBackup - SYSTEM_DATABASES - FULL', @step_id=1 ,
		@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES'', "https://myaccount.blob.core.windows.net/mycontainer"
		,  @Credential = ''AutoBackup_Credential'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = NULL, @CheckSum = ''Y'', @LogToTable = ''Y''" -b'
GO

If you need to restore your database then you can use the GUI and when you choose restore you will see this screen

Enter your storage account and the key which you can get from the Azure portal. You will notice that the credential has already been selected, click connect and

There are all of your backups ready to restore to any point in time that you choose. By clicking script the T-SQL is generated which looks like this


USE [master]
BACKUP LOG [Test] TO  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_LogBackup_2015-07-16_06-21-26.bak'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,
NOFORMAT, NOINIT,  NAME = N'Test_LogBackup_2015-07-16_06-21-26',
NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150714201240+00.bak'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150714202740+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150714224241+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715005741+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715031242+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715052742+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715074243+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715095743+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715121243+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Test] FROM  URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150716060004+00.log'
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NOUNLOAD,  STATS = 5
GO

There is an important note. Remember this when you have just set it up so that you don’t think that you have done it wrong (which is what I did!)

When you enable Automated Patching for the first time, Azure configures the SQL Server IaaS Agent in the background. During this time, the portal will not show that Automated Patching is configured. Wait several minutes for the agent to be installed, configured. After that the portal will reflect the new settings.

From <https://msdn.microsoft.com/en-us/library/azure/dn961166.aspx>

And also look out for this

The password I had chosen was not complex enough but the Powershell script had succeeded and not given me the warning

To set up SQL Automated Patching you follow a similar steps. The setting is again on the OS Config blade and click enable and then you can choose the frequency and duration of the patching.

It is important to remember to choose your maintenance window correctly. If you have set up your SQL VMs correctly you will have them in an availability set and be using either mirroring or Availability Groups and have the VMs set up in the same availability set to ensure availability during the underlying host patching but I had it confirmed by Principal Software Engineering Manager Sethu Srinivasan t via Microsoft PFE Arvind Shyamsundar b | t that the SQL Automated Patching is not HA aware so you will need to ensure that you set the maintenance windows on each VM to ensure that they do not overlap

Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information

Posted on September 7, 2014 by RobSewell

A short post today to pass on a script I wrote to fulfil a requirement I had.

Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.

I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object

 
$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]

Then by piping the database object to Get-Member I can see the properties

Lets take a look at the table object in the same way

I can see the indexes object so I pipe that to Get-Member as well

Now I have enough to information to create the report. I will select the Name, Table, Type and Space Used of the Indexes and format them nicely

$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|Format-Table –AutoSize

and here are the results

However, you may want the results to be displayed in a different manner, maybe CSV,HTML or text file and you can do this as follows

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Csv c:\temp\filegroups.csv
Invoke-Item c:\temp\filegroups.csv

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed| Out-File c:\temp\filegroups.txt
Invoke-Item c:\temp\filegroups.txt

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Html |Out-File c:\temp\filegroups.html
Invoke-Item c:\temp\filegroups.html

Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it

List Databases (and Properties) on SQL Server with PowerShell

Posted on September 11, 2013 by RobSewell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Todays question which I often get asked is What databases are on that server?

This is often a follow up to a question that requires the Find-Database script. It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation

Simply call it with Show-DatabasesOnServer SERVERNAME and use the results as you need

This only shows you the name but if you need more information about your databases then have a look and see what you require.

Use Get-Member to see what is there. I ran the following code to count the number of Properties available for Databases (Using PowerShell V3 on SQL Server 2012 SP1 11.0.3350.0 )

154 Properties that you can examine and that is just for databases:-)

Picking out a few properties you could do something like this

If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

So select Name becomes

select @{label="Database Name";Expression={$_.Name}}

The Column Heading is Database Name and the data is the Name property

and select Size becomes

Select @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places

then your code would look like this

$srv.databases|select @{label="Server";Expression={$_.Parent.name}},` 
@{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,` 
RecoveryModel, @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}|` 
Format-Table -Wrap –AutoSize

and the results

and here is the full code

<#PSScriptInfo

.VERSION 1.0

.GUID 48bf0316-66c3-4253-9154-6fc5b28e482a

.AUTHOR Rob Sewell

.DESCRIPTION Returns Database Name and Size in MB for databases on a SQL server
      
.COMPANYNAME 

.COPYRIGHT 

.TAGS SQL, Database, Databases, Size

.LICENSEURI 

.PROJECTURI 

.ICONURI 

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS 

.EXTERNALSCRIPTDEPENDENCIES 

.RELEASENOTES

#>
<#
    .Synopsis
    Returns the databases on a SQL Server and their size
    .DESCRIPTION
    Returns Database Name and Size in MB for databases on a SQL server
    .EXAMPLE
    Show-DatabasesOnServer

    This will return the user database names and sizes on the local machine default instance
    .EXAMPLE
    Show-DatabasesOnServer -Servers SERVER1

    This will return the database names and sizes on SERVER1
    .EXAMPLE
    Show-DatabasesOnServer -Servers SERVER1 -IncludeSystemDatabases

    This will return all of the database names and sizes on SERVER1 including system databases
    .EXAMPLE
    Show-DatabasesOnServer -Servers 'SERVER1','SERVER2\INSTANCE'

    This will return the user database names and sizes on SERVER1 and SERVER2\INSTANCE
    .EXAMPLE
    $Servers = 'SERVER1','SERVER2','SERVER3'
    Show-DatabasesOnServer -Servers $servers|out-file c:\temp\dbsize.txt

    This will get the user database names and sizes on SERVER1, SERVER2 and SERVER3 and export to a text file c:\temp\dbsize.txt
    .NOTES
    AUTHOR : Rob Sewell http://sqldbawithabeard.com
    Initial Release 22/07/2013
    Updated with switch for system databases added assembly loading and error handling 20/12/2015
    Some tidying up and ping check 01/06/2016
    
#>

Function Show-DatabasesOnServer 
{
    [CmdletBinding()]
    param (
        # Server Name or array of Server Names - Defaults to $ENV:COMPUTERNAME
        [Parameter(Mandatory = $false, 
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true, 
            Position = 0)]
        $Servers = $Env:COMPUTERNAME,
        # Switch to include System Databases
        [Parameter(Mandatory = $false)]
        [switch]$IncludeSystemDatabases
    )
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
    foreach ($Server in $Servers) {
        if ($Server.Contains('\')) {
            $ServerName = $Server.Split('\')[0]
            $Instance = $Server.Split('\')[1]
        }
        else {
            $Servername = $Server
        } 

        ## Check for connectivity
        if ((Test-Connection $ServerName -count 1 -Quiet) -eq $false) {
            Write-Error "Could not connect to $ServerName - Server did not respond to ping"
            $_.Exception
            continue
        }
    
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server

        if ($IncludeSystemDatabases) {
            try {
                $Return = $srv.databases| Select Name, Size
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        else {
            try {
                $Return = $srv.databases.Where{$_.IsSystemObject -eq $false}| Select Name, Size
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        Write-Output "`n The Databases on $Server and their Size in MB `n"
        $Return
    }
}

Creating SQL Server Database with PowerShell

Posted on September 8, 2013 by RobSewell

This morning I have been setting up my Azure Servers in preparation for my presentation to the Cardiff SQL User Group this month.

I used my scripts from My Post on Spinning Up Azure SQL Boxes to create two servers and then I wanted to create some databases

I decided it was time to write a Create-Database function using a number of scripts that I have used to create individual databases.

Errors

Whilst finalising the function I didn’t quite get it right sometimes and was faced with an error.

Not the most useful of errors to troubleshoot. The issue could be anywhere in the script

You can view the last errors PowerShell has shown using $Errors. This gives you the last 500 errors but you can see the last error by using $Error[0] if you pipe it to Format-List you can get a more detailed error message so I added a try catch to the function which gave me an error message I could resolve.

Much better. The problem was

Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\.LDF’ because it already exists.

Mistyping a variable has caused this. Creating an empty file name variable which then threw the error the second(and third,fourth fifth) times I ran the script but this error pointed me to it.

Creating Database

There are a vast number of variables you can set when creating a database. I decided to set File Sizes, File Growth Sizes, Max File Sizes and Recovery Model. I only set Server and Database Name as mandatory parameters and gave the other parameters default values

We take the parameters for file sizes in MB and set them to KB

Then set the default file locations. Create a database object, a Primary file group object and add the file group object to the database object

Add a User File Group for User objects

Create a database file on the primary file group using the variables set earlier

Do the same for the user file and then create a Log File

Set the Recovery Model and create the database and then set the user file group as the default

Finally catch the errors

It can then be called as follows Create-Database SERVERNAME DATABASENAME

or by setting all the parameters Create-Database -Server Fade2black -DBName DatabaseTest -SysFileSize 10 -UserFileSize 15 -LogFileSize 20 -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL

This means that I can easily and quickly set up several databases of different types and sizes

The script can be found here

#############################################################################################
#
# NAME: Create-Database.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:08/09/2013
#
# COMMENTS: Load function for creating a database
#           Only Server and DB Name are mandatory the rest will be set to small defaults
#
# USAGE:  Create-Database -Server Fade2black -DBName Test35 -SysFileSize 10 -UserFileSize 15 -LogFileSize 20
# -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL
# ————————————————————————


Function Create-Database {
    Param(
        [Parameter(Mandatory = $true)]
        [String]$Server ,
        [Parameter(Mandatory = $true)]
        [String]$DBName,
        [Parameter(Mandatory = $false)]
        [int]$SysFileSize = 5,
        [Parameter(Mandatory = $false)]
        [int]$UserFileSize = 25,
        [Parameter(Mandatory = $false)]
        [int]$LogFileSize = 25,
        [Parameter(Mandatory = $false)]
        [int]$UserFileGrowth = 5,
        [Parameter(Mandatory = $false)]
        [int]$UserFileMaxSize = 100,
        [Parameter(Mandatory = $false)]
        [int]$LogFileGrowth = 5,
        [Parameter(Mandatory = $false)]
        $LogFileMaxSize = 100,
        [Parameter(Mandatory = $false)]
        [String]$DBRecModel = 'FULL'
    )

    try {
        # Set server object
        $srv = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $server
        $DB = $srv.Databases[$DBName]
    
        # Define the variables
        # Set the file sizes (sizes are in KB, so multiply here to MB)
        $SysFileSize = [double]($SysFileSize * 1024.0)
        $UserFileSize = [double] ($UserFileSize * 1024.0)
        $LogFileSize = [double] ($LogFileSize * 1024.0)
        $UserFileGrowth = [double] ($UserFileGrowth * 1024.0)
        $UserFileMaxSize = [double] ($UserFileMaxSize * 1024.0)
        $LogFileGrowth = [double] ($LogFileGrowth * 1024.0)
        $LogFileMaxSize = [double] ($LogFileMaxSize * 1024.0)
   

        Write-Output "Creating database: $DBName"
 
        # Set the Default File Locations
        $DefaultDataLoc = $srv.Settings.DefaultFile
        $DefaultLogLoc = $srv.Settings.DefaultLog
 
        # If these are not set, then use the location of the master db mdf/ldf
        if ($DefaultDataLoc.Length -EQ 0) {$DefaultDataLoc = $srv.Information.MasterDBPath}
        if ($DefaultLogLoc.Length -EQ 0) {$DefaultLogLoc = $srv.Information.MasterDBLogPath}
 
        # new database object
        $DB = New-Object ('Microsoft.SqlServer.Management.SMO.Database') ($srv, $DBName)
 
        # new filegroup object
        $PrimaryFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($DB, 'PRIMARY')
        # Add the filegroup object to the database object
        $DB.FileGroups.Add($PrimaryFG )
 
        # Best practice is to separate the system objects from the user objects.
        # So create a seperate User File Group
        $UserFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($DB, 'UserFG')
        $DB.FileGroups.Add($UserFG)
 
        # Create the database files
        # First, create a data file on the primary filegroup.
        $SystemFileName = $DBName + "_System"
        $SysFile = New-Object ('Microsoft.SqlServer.Management.SMO.DataFile') ($PrimaryFG , $SystemFileName)
        $PrimaryFG.Files.Add($SysFile)
        $SysFile.FileName = $DefaultDataLoc + $SystemFileName + ".MDF"
        $SysFile.Size = $SysFileSize
        $SysFile.GrowthType = "None"
        $SysFile.IsPrimaryFile = 'True'
 
        # Now create the data file for the user objects
        $UserFileName = $DBName + "_User"
        $UserFile = New-Object ('Microsoft.SqlServer.Management.SMO.Datafile') ($UserFG, $UserFileName)
        $UserFG.Files.Add($UserFile)
        $UserFile.FileName = $DefaultDataLoc + $UserFileName + ".NDF"
        $UserFile.Size = $UserFileSize
        $UserFile.GrowthType = "KB"
        $UserFile.Growth = $UserFileGrowth
        $UserFile.MaxSize = $UserFileMaxSize
 
        # Create a log file for this database
        $LogFileName = $DBName + "_Log"
        $LogFile = New-Object ('Microsoft.SqlServer.Management.SMO.LogFile') ($DB, $LogFileName)
        $DB.LogFiles.Add($LogFile)
        $LogFile.FileName = $DefaultLogLoc + $LogFileName + ".LDF"
        $LogFile.Size = $LogFileSize
        $LogFile.GrowthType = "KB"
        $LogFile.Growth = $LogFileGrowth
        $LogFile.MaxSize = $LogFileMaxSize
 
        #Set the Recovery Model
        $DB.RecoveryModel = $DBRecModel
        #Create the database
        $DB.Create()
 
        #Make the user filegroup the default
        $UserFG = $DB.FileGroups['UserFG']
        $UserFG.IsDefault = $true
        $UserFG.Alter()
        $DB.Alter()

        Write-Output " $DBName Created"
        Write-Output "System File"
        $SysFile| Select Name, FileName, Size, MaxSize, GrowthType| Format-List
        Write-Output "User File"
        $UserFile| Select Name, FileName, Size, MaxSize, GrowthType, Growth| Format-List
        Write-Output "LogFile"
        $LogFile| Select Name, FileName, Size, MaxSize, GrowthType, Growth| Format-List
        Write-Output "Recovery Model"
        $DB.RecoveryModel

    }
    Catch {
        $error[0] | fl * -force
    }
}

Using PowerShell to find a database amongst hundreds

Posted on September 7, 2013 by RobSewell

As you know, I love PowerShell!

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which server is that database on?

It isn’t always asked like that. Sometimes it is a developer asking where the database for their UAT/SAT/FAT/Dev environment is. Sometimes it is a manager who requires the information for some documentation or report. I wrote it because whilst I am good, I am not good enough to remember the server location for many hundreds of databases and also because I wanted to enable a new starter to be as self sufficient as possible

When I first wrote this I thought it would be a case of simply using $databaseName.Contains($DatabaseNameSearch) but this did not work for MiXed case searches or DatAbaSe names so I had to convert both the search and the database name to lower case first

I create an empty hash table and then populate it with the results

Set a results variable to the names from the hash table and count the number of records

and call it like this

Note that the search uses the contains method so no need for wildcards

Results come out like this

You can find the code here

#############################################################################################
#
# NAME: Find-Database.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Load function for finding a database
# USAGE: Find-Database DBName
# ������������������������


Function Find-Database ([string]$Search) {

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Pull a list of servers from a local text file

    $servers = Get-Content 'sqlservers.txt'

    #Create an empty Hash Table
    $ht = @{}
    $b = 0

    #Convert Search to Lower Case
    $DatabaseNameSearch = $search.ToLower()  

				Write-Output "#################################"
				Write-Output "Searching for $DatabaseNameSearch "  
				Write-Output "#################################"  

                                 

    #loop through each server and check database name against input
                    
    foreach ($server in $servers) {

        if (Test-Connection $Server -Count 1 -Quiet) {
            $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
            foreach ($database in $srv.Databases) {
                $databaseName = $database.Name.ToLower()

                if ($databaseName.Contains($DatabaseNameSearch)) {

                    $DatabaseNameResult = $database.name
                    $Key = "$Server -- $DatabaseNameResult"
                    $ht.add($Key , $b)
                    $b = $b + 1
                }
            }        
        }
    }

    $Results = $ht.GetEnumerator() | Sort-Object Name|Select Name
    $Resultscount = $ht.Count

    if ($Resultscount -gt 0) {

        Write-Output "###############   I Found It!!  #################"
        foreach ($R in $Results) {
            Write-Output $R.Name 
        }
    }
    Else {
        Write-Output "############    I am really sorry. I cannot find"  $DatabaseNameSearch  "Anywhere  ##################### "
    }             
}

SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Posted on September 5, 2013 by RobSewell

Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.

I created a SQL Server Object

One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object

$server | Get-Member

gave me the JobServer Property

$Server.JobServer|gm

includes the Operator Property

$Server.JobServer.Operators | gm

has the EnumJobNotifications and EnumNotifications methods

So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator

and create a simple report

However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.

So I ended up with two functions

and I thought I could do this

But that doesnt work

So I tried this

and that doesnt work either

Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.

Job Notifications are System.Object and Alert Notifications are System.Array

I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.

So the final script is as follows

and the script is

#############################################################################################
#
# NAME: Show-SQLServerOperators.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:03/09/2013
#
# COMMENTS: Load function for Enumerating Operators and Notifications
# ————————————————————————

Function Show-SQLServerOperators ($SQLServer) {
    Write-Output "############### $SQLServer ##########################"
    Write-Output "#####################################################`n"     

    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
        
        
    foreach ($Operator in $server.JobServer.Operators) {
        $Operator = New-Object ("$SMO.Agent.Operator") ($server.JobServer, $Operator)

        $OpName = $Operator.Name
        Write-Output "Operator $OpName"
        Write-Output "`n###### Job Notifications   ######"
        $Operator.EnumJobNotifications()| Select JobName | Format-Table
        Write-Output "#####################################################`n"  
        Write-Output "`n###### Alert Notifications  #######"
        $Operator.EnumNotifications() | Select AlertName | Format-Table
        Write-Output "#####################################################`n"  
                 
    }
 
}

Those Pesky ‘s

Posted on February 11, 2013 by RobSewell

Changing Domain Names in a Column

A quick little post for today. Not particularly SQL related but the points at the end are relevant.

I had a task when moving a service to a new development area to change the domain name within columns in several tables from “DOMAIN1\USER” to “DOMAIN2\USER”

In SQL I was able to do this quite easily as follows

   USE [DATABASENAME] 
    GO
    -- Declare variables 
    DECLARE @Live nvarchar(10) 
    DECLARE @Dev nvarchar(10) 
    
    -- Set the variable to the Domains 
    Set @Live = 'Live Domain' 
    Set @Dev = 'Dev Domain' 
    
    --Update tables 
    UPDATE [TABLENAME] 
    SET [User] = REPLACE([User], @Live, @Dev) 
    GO 
    UPDATE [TABLENAME] 
    SET [Group] = REPLACE([Group], @Live, @Dev) 
    GO

I also had to do the same for some Oracle databases too and this is where the fun started!

I needed to create the update scripts for documentation for the Oracle databases.

I wanted to create

  update schema.tablename set userid = replace ('DOMAIN1\USER', 'DOMAIN1', 'DOMAIN2') WHERE USERID = 'DOMAIN1\USER';

for each userid in the table.I had trouble with the script I found in our DBA area as it kept failing with

ORA-00911: invalid character

at the \

as it wouldn’t add the ‘ ‘ around DOMAIN1\USER

Not being an Oracle DBA but wanting to solve the issue once and for all I tried a whole host of solutions trying to find the escape character. i asked the Oracle DBAs but they were unable to help Checking the IT Pros handbook (also known as Google!) made me more confused but in the end I solved it.

   select 'update schema.table set userid = replace (''' || userid || ''', ''DOMAIN1'', ''DOMAIN2'') WHERE USERID = ''' || USERID || ''';' FROM schema.tablename;

A whole host of ‘s in there!!

I put this in my blog as it is relevant to my situation and an experience I have had that I couldn’t easily solve. Maybe it will help another person searching for the same thing.

It raises some interesting points

The script provided ( I use that term loosely, it had the right name and was in the right place to use for this process) had obviously not been run as it didn’t work or someone had manually added the ‘s. I wasn’t go to do that for the number of users required.

If it no good, if it doesn’t do what i expected or is still in development then mark it as so, so that everyone knows. In the name of the script, in the comments in the script or by keeping live tested scripts in one place. Which ever method you choose is fine as long as it is appropriate to your environment and everyone knows about it

I probably say a dozen times a day to my new colleague

“In case you/I get run over by a bus”

It is all very well being the one who knows everything but it is pointless if you aren’t there SPOF’s (Single Points of Failure) apply to people as well as hardware.

Enable your service to be supported by preparing proper documentation.

This doesn’t have to be reams of paperwork. It can sometimes be as simple as placing things in a recognised place or a single comment in the script.

I hold my hands up. I am guilty of this too. I have been so busy I haven’t done this as much as I should have over the last few months of last year. I have tried but not done as well as I should have. In my defence, I have spent plenty of time recently rectifying this, which is why this situation was so memorable.

Some links I have read in the past related to this by people who know more than me.

Documentation It Doesn’t Suck – Brent Ozar

Your Lack Of Documentation is Costing you More than you Think – John Samson

Do You Document Your SQL Server Instances? – Brad McGhee

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Tag Archives: databases

Setting Up and Using Azure VM SQL Automated Backup (and Restore)

Like this:

Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information

Like this:

List Databases (and Properties) on SQL Server with PowerShell

Like this:

Creating SQL Server Database with PowerShell

Errors

Creating Database

Like this:

Using PowerShell to find a database amongst hundreds

Like this:

SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Like this:

Those Pesky ‘s

Changing Domain Names in a Column

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Errors

Creating Database

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Changing Domain Names in a Column

Share this:

Like this: