Skip to main content

Discover DEVELOPMENT databases set to FULL Recovery Model

Sometimes DBAs forget to set databases on DEVELOPMENT servers to SIMPLE recovery after a restore from PRODUCTION.

Here's a short PowerShell script that uses Sql Server Management Objects (SMO) to discover this condition.

Save it as get-full.ps1 on your machine and execute using the example code on Line 2.


Load a text file with your list of DEVELOPMENT servers like so...


Server1
Server2
Server3


Save the file on your machine to C:\Input\DevServers.

A csv file will be produced listing the server, database, and recovery model of the databases set to FULL recovery.





1:  # find databases in full recovery on DEVELOPMENT servers
2:  #.\get-full
3:   
4:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
5:   
6:  $start = get-date
7:  write-host "Start: " $start
8:   
9:  @(foreach ($svr in get-content "C:\Input\DevServers.txt")
10:  {
11:  $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
12:  trap {$s; continue } $s.databases | ? {$_.RecoveryModel.ToString() -eq 'Full'} | select parent, name, recoverymodel
13:  }
14:  ) | export-csv -noType C:\Output\DEVELOPMENT_DBs_in_FULL_RECOVERY.csv
15:   
16:  $end = get-date
17:  write-host "End: " $end

Comments

Popular posts from this blog

Modifying Endpoint URLs on Availability Group Replicas

I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas. 

The reason for this blog post is that I could not answer the following questions:

Do I need to suspend data movement prior to making this change? 

Would this change require a restart of the database instance?

I spent enough time searching on my own to no avail that I tossed the question to the #sqlhelp hashtag on Twitter and Slack but didn't get an answer prior to executing the change request.

After reading the relevant documentation, I think it's probably a good idea to suspend data movement for this change.

The T-SQL is straightforward. 

USE MASTER
GO

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://10.10.10.1:5022');

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON 'SQL2012-2' WITH (ENDPOINT_URL = 'TCP://10.10.10.2:5022');

ALTER AVAILABILITY GROUP [AG2] 
MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://10…

PowerShell: Quick SQL Server Version Check

I have to keep track of our SQL Server version inventory.  The goal is to reduce the SQL Server 2000 population as fast as possible.


The following PowerShell script will produce a csv file containing the database server name and the version of SQL Server it's running.


1: ## Get SQL Version installed on multiple servers ##2: ## ./sqlver.ps13: $start = get-date4: write-host "Start: " $start5:  6: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null7:  8: $FilePath = "C:\Output"9: $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")10:  11: # Version inventory12: @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt")13: {14: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr15: $s | select Name, Version16:  17: }) | export-csv -noType $OutFile18:  19: $end = get-date 2…

AzureRM Templates 101

I've recently started working with AzureRM templates to build new environments.

This document really helped me understand the template structure when I first started looking at them.
https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring-templates

I love examples when I'm trying to learn something new and the Quick Start templates are the mother lode. 
https://github.com/Azure/azure-quickstart-templates

Our goal is to incorporate our templates into an Azure Blueprint so that we can quickly build new environments when needed. AzureRM templates can be artifacts of a blueprint.
https://docs.microsoft.com/en-us/azure/governance/blueprints/overview