Tag Archives: Rationalise

Remove-SQLDatabaseSafely My First Contribution to DBATools

Posted on by

What is DBA Tools?

A collection of modules for SQL Server DBAs. It initially started out as ‘sqlmigration’, but has now grown into a collection of various commands that help automate DBA tasks and encourage best practices.

You can read more about here and it is freely available for download on GitHub I thoroughly recommend that you watch this quick video to see just how easy it is to migrate an entire SQL instance in one command (Longer session here )

Installing it is as easy as

Install-Module dbatools

which will get you over 80 commands . Visit https://dbatools.io/functions/ to find out more information about them

cmdlets

The journey to Remove-SQLDatabaseSafely started with William Durkin b | t who presented to the SQL South West User Group  (You can get his slides here)

Following that session  I wrote a Powershell Script to gather information about the last used date for databases which I blogged about here and then a T-SQL script to take a final backup and create a SQL Agent Job to restore from that back up which I blogged about here The team have used this solution (updated to load the DBA Database and a report instead of using Excel) ever since and it proved invaluable when a read-only database was dropped and could quickly and easily be restored with no fuss.

I was chatting with Chrissy LeMaire who founded DBATools b | t about this process and when she asked for contributions in the SQL Server Community Slack I offered my help and she suggested I write this command. I have learnt so much. I thoroughly enjoyed and highly recommend working on projects collaboratively to improve your skills. It is amazing to work with such incredible professional PowerShell people.

I went back to the basics and thought about what was required and watched one of my favourite videos again. Grant Fritcheys Backup Rant

I decided that the process should be as follows

  1. Performs a DBCC CHECKDB
  2. Database is backed up WITH CHECKSUM
  3. Database is restored with VERIFY ONLY on the source
  4. An Agent Job is created to easily restore from that backup
  5. The database is dropped
  6. The Agent Job restores the database
  7. performs a DBCC CHECKDB and drops the database for a final time

This (hopefully) passes all of Grants checks. This is how I created the command

I check that the SQL Agent is running otherwise we wont be able to run the job. I use a while loop with a timeout like this

$agentservice = Get-Service -ComputerName $ipaddr -Name $serviceName
if ($agentservice.Status -ne 'Running') {
    $agentservice.Start()
    $timeout = new-timespan -seconds 60
    $sw = [diagnostics.stopwatch]::StartNew()
    $agentstatus = (Get-Service -ComputerName $ipaddr -Name $serviceName).Status
    while ($dbStatus -ne 'Running' -and $sw.elapsed -lt $timeout) {
        $dbStatus = (Get-Service -ComputerName $ipaddr -Name $serviceName).Status
    }
}

There are a lot more checks and logic than I will describe here to make sure that the process is as robust as possible. For example, the script can exit after errors are found using DBCC CHECKDB or continue and label the database backup file and restore job appropriately. Unless the force option is used it will exit if the job name already exists. We have tried to think of everything but if something has been missed or you have suggestions let us know (details at end of post)

The only thing I didn’t add was a LARGE RED POP UP SAYING ARE YOU SURE YOU WANT TO DROP THIS DATABASE but I considered it!!

Performs a DBCC CHECKDB

Running DBCC CHECKDB with Powershell is as easy as this

$sourceserver = New-Object Microsoft.SQLServer.Management.Smo.Server "ServerName"
$db = $sourceserver.databases[$dbname]
$null = $db.CheckTables('None')

you can read more on MSDN

Database is backed up WITH CHECKSUM

Stuart Moore is my go to for doing backups and restores with SMO

I ensured that the backup was performed with checksum like this

$backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$backup.Action = [Microsoft.SqlServer.Management.SMO.BackupActionType]::Database
$backup.BackupSetDescription = "Final Full Backup of $dbname Prior to Dropping"
$backup.Database = $dbname
$backup.Checksum = $True

Database is restored with VERIFY ONLY on the source

I used SMO all the way through this command and performed the restore verify only like this

$restoreverify = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
$restoreverify.Database = $dbname
$restoreverify.Devices.AddDevice($filename, $devicetype)
$result = $restoreverify.SqlVerify($sourceserver)

An Agent Job is created to easily restore from that backup

First I created a category for the Agent Job

Function New-SqlAgentJobCategory {
    param ([string]$categoryname,
        [object]$jobServer)
    if (!$jobServer.JobCategories[$categoryname]) {
        if ($Pscmdlet.ShouldProcess($sourceserver, "Creating Agent Job Category $categoryname")
            {
                try {
                    Write-Output "Creating Agent Job Category $categoryname"
                    $category = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobCategory
                    $category.Parent = $jobServer
                    $category.Name = $categoryname
                    $category.Create()
                    Write-Output "Created Agent Job Category $categoryname"
                }
                catch {
                    Write-Exception $_
                    throw "FAILED : To Create Agent Job Category $categoryname - Aborting"
                }
            }
        }
    }
}

and then generated the TSQL for the restore step by using the script method on the Restore SMO object

This is how to create an Agent Job

$job = New-Object Microsoft.SqlServer.Management.Smo.Agent.Job $jobServer, $jobname
$job.Name = $jobname
$job.OwnerLoginName = $jobowner
$job.Description = "This job will restore the $dbname database using the final backup located at $filename"

and then to add a job step to run the restore command

$jobStep = new-object Microsoft.SqlServer.Management.Smo.Agent.JobStep $job, $jobStepName $jobStep.SubSystem = 'TransactSql' # 'PowerShell' 
$jobStep.DatabaseName = 'master' 
$jobStep.Command = $jobStepCommmand 
$jobStep.OnSuccessAction = 'QuitWithSuccess' 
$jobStep.OnFailAction = 'QuitWithFailure' 
if ($Pscmdlet.ShouldProcess($destination, "Creating Agent JobStep on $destination")
    { 
        $null = $jobStep.Create()
    } 
    $job.ApplyToTargetServer($destination)
    $job.StartStepID = $jobStartStepid 
    $job.Alter()

 

The database is dropped

We try 3 different methods to drop the database

$server.KillDatabase($dbname)
$server.databases[$dbname].Drop()
$null = $server.ConnectionContext.ExecuteNonQuery("DROP DATABASE ")

The Agent Job restores the database

To run the Agent Job I call the start method of the Job SMO Object
    $job = $destserver.JobServer.Jobs[$jobname]
    $job.Start()
    $status = $job.CurrentRunStatus
    while ($status -ne 'Idle') {
        Write-Output " Restore Job for $dbname on $destination is $status"
        $job.Refresh()
        $status = $job.CurrentRunStatus
        Start-Sleep -Seconds 5
    }
Then we drop the database for the final time with the confidence that we have a safe backup and an easy one click method to restore it from that backup (as long as the backup is in the same location)
There are further details on the functions page on dbatools
Some videos of it in action are on YouTube http://dbatools.io/video
You can take a look at the code on GitHub here

You can install it with

Install-Module dbatools
You can provide feedback via the Trello Board or discuss it in the #dbatools channel in the Sqlserver Community Slack
You too can also become a contributor https://dbatools.io/join-us/ Come and write a command to make it easy for DBAs to (this bit is up to your imagination).

Rationalisation of Database with Powershell and T-SQL part two

Posted on by

In the previous post I showed the script to create an Excel Workbook, colour coded showing the last used date for all of the databases on servers in my sqlservers.txt file. After gathering that information over several months, there is then a requirement for someone to make a decision as to which databases can be removed.

Obviously there will be some databases that are read-only or if not set specifically as read-only may only be used for reference without data being added. You should hopefully have knowledge of these databases and be able to take them off the list quickly.

There are other challenges for a DBA to overcome prior to any action. Many questions need to be answered such as

Who owns the database?
Who is the service owner responsible for the service/application in use by the database?
Even though they may be the service owner who will ultimately sign off permission to remove the database are they aware of how important it is for their people? Or what times of the year it is important to them?
You may find test and development databases that have not been used for months but will they be required next week?
Is it important enough for them to take the time to give the permission?

And plenty more… Add some in the comments below.

Our Primary responsibility is the data. We need to be able to ensure that the data is safe and can be made available quickly and easily. In this situation we need to have a valid backup and a quick and easy method of restoring it. I chose to solve this by creating a T-SQL script which will :-

The reasoning for these steps is best explained by watching this video and yes I always perform the last step too J

I could have used PowerShell to do this by examining The SMO for the Server and the JobServer but this time I decided to challenge myself by writing it in T-SQL as I am weaker in that area. The script below is the result of that work. It works for me. I expect that there are other ways of doing this and please feel free to point out any errors or suggestions. That is how I learn. Hopefully these posts will be of use to other DBAs like myself.

As always with anything you read on the internet. Validate and test. This script works for me on SQL Servers 2005, 2008,2008R2 and 2012 but if you are thinking of running it in your own Production Environment – DON’T.

Well not until you have tested it somewhere safe first J

The first challenge I encountered was that I wanted to only have to change the name of the database to be able to run the script and perform all of these steps. That will also lead onto a stored procedure and then I can automate more of this process and schedule at times to suit the database servers as well. I accomplished this by using a temp table and populating it with the variables I will need as shown below

-- Drop temp table if it exists
IF OBJECT_ID('tempdb..#vars') IS NOT NULL
DROP TABLE #vars 
-- Create table to hold global variable
create table #vars (DBName nvarchar(50), PATH nvarchar(300),DataName nvarchar(50),LogName nvarchar (50),DataLoc nvarchar (256),LogLoc nvarchar (256))
insert into #vars (DBName) values ('DATABASENAME')
-- Declare and set variables
DECLARE @PATH nvarchar(300)
Set @Path = (SELECT 'PATH TO RATIONALISATION FOLDER WITH TRAILING SLASH' + @DBName + '_LastGolden_' + + convert(varchar(50),GetDate(),112) + '.bak' )
DECLARE @DataName nvarchar(50)
Set @DataName = (SELECT f.name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)
-- Print @DataName
DECLARE @LogName nvarchar (50)
Set @LogName = (SELECT f.name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)
-- PRINT @LogName
Declare @DataLoc nvarchar (256)
Set @DataLoc = (SELECT f.physical_name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)
--Print @DataLoc
Declare @LogLoc nvarchar (256)
Set @LogLoc = (SELECT f.physical_name
FROM sys.master_files F
join sys.databases D
on d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)
--Print @LogLoc
update #vars Set PATH = @PATH
update #vars Set DataName = @DataName
update #vars Set LogName = @LogName
update #vars Set DataLoc = @DataLoc
update #vars Set LogLoc = @LogLoc
-- Select * from #vars

I then use the variables throughout the script by selecting them from the temp table as follows

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

And using the variables to create and execute the T-SQL for each of the steps above.

It is pointless to move onto the next step of the previous one has failed so I created some error handling as follows

if @@error != 0 raiserror('Rationalisation Script failed at Verify Restore', 20, -1) with log
GO

I created the T-SQL for the agent job by first creating the restore script and adding it to a variable and then right-clicking on a previously created restore database job and using the script to new window command

It was then a case of adding single quotes and reading the code until it would successfully run

/***
Rationalisation Script

Script to Automatically Backup, Drop and create Agent Job to restore from that backup

AUTHOR - Rob Sewell http://sqldbawithabeard.com
DATE - 19/01/2014

USAGE - You need to Change the Database Name after " insert #vars values (' "
		You also need to check that the folder after " Set @Path = (SELECT ' " is correct and exists 
		and Find and replace both entries for THEBEARD\Rob with the account that will be the owner of the job and the database owner
		
Once this has been run AND you have checked that it has successfully backed up the database and created the job and you have checked hte job works
You may delete the backups but keep the backup folder under UserDbs

***/

 --Drop temp table if it exists 
IF OBJECT_ID('tempdb..#vars') IS NOT NULL
DROP TABLE #vars	

--Create table to hold global variable
create table #vars (DBName nvarchar(50), PATH nvarchar(300),DataName nvarchar(50),LogName nvarchar (50),DataLoc nvarchar (256),LogLoc nvarchar (256))
insert into #vars (DBName) values ('SQL2012Ser2012DB'
					)

--Declare and set variables	

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)			

DECLARE @PATH nvarchar(300)
Set @Path = (SELECT 'PATH TO RATIONALISATION FOLDER' + @DBName + '_LastGolden_' + + convert(varchar(50),GetDate(),112) + '.bak' )

DECLARE @DataName nvarchar(50)
Set @DataName = (SELECT f.name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)

--Print @DataName

DECLARE @LogName nvarchar (50)
Set @LogName = (SELECT f.name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)

--PRINT @LogName

Declare @DataLoc nvarchar (256)
Set @DataLoc = (SELECT f.physical_name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 0
AND d.Name = @DBNAME)

--Print @DataLoc

Declare @LogLoc nvarchar (256)
Set @LogLoc = (SELECT f.physical_name
FROM sys.master_files F 
join sys.databases D
on
d.database_id = f.database_id
WHERE F.type = 1
AND d.Name = @DBNAME)

--Print @LogLoc

update #vars Set PATH = @PATH 
update #vars Set DataName = @DataName
update #vars Set LogName = @LogName
update #vars Set DataLoc = @DataLoc
update #vars Set LogLoc = @LogLoc

-- Select * from #vars
-- DBCC

DECLARE @DBCCSQL nvarchar (4000)
SET @DBCCSQL = '
USE [' + @DBName + ']
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
'
-- Print @DBCCSQL

EXECUTE(@DBCCSQL)

-- Break out if error raised We need to do some work if there are errors here

if @@error != 0 raiserror('Rationalisation Script failed at DBCC', 20, -1) with log
GO

-- Declare and set variables	
			
DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

DECLARE @PATH nvarchar(300)
Set @Path = (SELECT PATH from #vars)

Declare @BKUPName nvarchar(300)
Set @BKUPName = (SELECT 'Last Golden Backup For ' + @DBName + '- Full Database Backup')

DECLARE @BackupSQL nvarchar (4000)
SET @BackupSQL = '
BACKUP DATABASE [' + @DBName + '] TO  DISK = N''' + @PATH + '''
WITH INIT,  NAME = N''' + @BKUPName + ''', 
CHECKSUM, STATS = 10
'

--- PRINT @BackupSQL

-- Backup database to Golden backup location

EXECUTE(@BackupSQL)
GO

-- Break Out if there are errors here - If there is no backup we don't want to continue

if @@error != 0 raiserror('Rationalisation Script failed at Backup', 20, -1) with log
GO

DECLARE @PATH nvarchar(300)
Set @Path = (SELECT PATH from #vars)

RESTORE VERIFYONLY
FROM DISK = @PATH;

if @@error != 0 raiserror('Rationalisation Script failed at Verify Restore', 20, -1) with log
GO
-- Declare variables for dropping database

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

DECLARE @DROPSQL nvarchar (4000)
SET @DROPSQL = '
USE [master]
ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [' + @DBName + '] 
'
-- PRINT @DROPSQL

--Drop database

EXECUTE(@DROPSQL)
GO
if @@error != 0 raiserror('Rationalisation Script failed at Drop Database', 20, -1) with log
GO

--Declare variables for creating Job

DECLARE @DBName nvarchar(50)
Set @DBName = (Select DBNAme from #vars)

DECLARE @PATH nvarchar(300)
Set @Path = (Select PATH from #vars)

DECLARE @DataName nvarchar(50)
Set @DataName = (Select DataName from #vars)

DECLARE @LogName nvarchar (50)
Set @LogName = (Select LogName from #vars)

Declare @DataLoc nvarchar (256)
Set @DataLoc = (Select DataLoc from #vars)

Declare @LogLoc nvarchar (256)
Set @LogLoc = (Select LogLoc from #vars)

DECLARE @RestoreCommand nvarchar(4000)
Set @RestoreCommand = '''RESTORE DATABASE [' + @DBName + '] 
FROM  DISK = N''''' + @PATH + '''''
WITH  FILE = 1,  
MOVE N''''' + @DataName +  ''''' TO N''''' + @DataLoc + ''''',  
MOVE N''''' + @LogName + ''''' TO N''''' + @LogLoc + ''''',  
NOUNLOAD,  REPLACE,  STATS = 10

'''
--print @RestoreCommand

--Create Job creation tsql

DECLARE @JOBSQL nvarchar (4000)
SET @JOBSQL = 'USE [msdb]

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/18/2014 14:12:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''[Uncategorized (Local)]'' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''[Uncategorized (Local)]''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @JOBNAME nvarchar(300)
set @JOBNAME = ''Rationlised - - Restore '  + @DBName + ' from Last Golden Backup''

Declare @JobDesc nvarchar(300)
Set @JobDesc = '' Rationalised Database Restore Script for ' + @DBName + '''

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name= @JOBNAME, 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=@JobDesc, 
		@category_name=N''[Uncategorized (Local)]'', 
		@owner_login_name=N''THEBEARD\Rob'', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore Database]    Script Date: 01/18/2014 14:12:04 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Restore Database'', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N''TSQL'', 
		@command= ' + @RestoreCommand + ', 
		@database_name=N''master'', 
		@flags=4
/****** Object:  Step [Set Owner]    Script Date: 01/19/2014 10:14:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Set Owner'', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N''TSQL'', 
		@command=N''USE [' + @DBName + ']

EXEC sp_changedbowner @loginame = N''''THEBEARD\Rob'''', @map = false'', 
		@database_name=N''master'', 
		@flags=0		
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


'
--PRINT @JOBSQL

--Create Agent Job

EXECUTE(@JOBSql)

if @@error != 0 raiserror('Rationalisation Script failed at Create Job', 20, -1) with log
GO

DROP Table #vars

 

The process I have used is to change the database name in the script and run it and then run the Agent Job and check the database has been created. Then and only then can I drop the database and disable any jobs for the database. Yes that was the last step in the video J as Grant says “a file is just a file, a backup is a restored database”

Using this script you can reduce the footprint and load on your servers by removing unneeded or unused databases whilst still guaranteeing that should there be a requirement for them you KNOW you can easily restore them. You will still need to take some additional steps like adding a stop to the Agent Job to recreate any users and any other jobs that the database needs but that is more specific to your environment and you will be best placed to achieve this


Rationalisation of Database with Powershell and T-SQL part one

Posted on by

I have recently been involved in a project to rationalise databases. It is easy in a large organisation for database numbers to rapidly increase and sometimes the DBA may not be aware of or be able to control the rise if they don’t have knowledge of all of the database servers on the estate.

There are lots of benefits of rationalisation to the business. Reduced cpu usage = reduced heat released = lower air-con bill for the server room and less storage used = quicker backups and less tapes used or better still less requirement for that expensive new SAN. You may be able to consolidate data and provide one version of the truth for the business as well. Removing servers can release licensing costs which could then be diverted elsewhere or pay for other improvements.

William Durkin b | t presented to the SQL South West User Group about this and will be doing the session at SQL Saturday in Exeter in March 2014 Please check out his session for a more detailed view

I needed to be able to identify databases that could possibly be deleted and realised that an easy way to achieve this would be to use a script to check for usage of the database.

No need to recreate the wheel so I went to Aaron Bertrands blog http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx and used his script. Instead of using an audit file I decided to use Powershell so that I could output the results to Excel and colour code them. This made it easier to check the results and also easier to show to Managers and Service Owners

#################################################################################
# NAME: lastdbusage.ps1
# AUTHOR: Rob Sewell
# http://sqldbawithabeard.com
# DATE:19/10/2013
#
# COMMENTS: Fill Excel WorkBook with details fo last access times for each database
#
# NOTES : Does NOT work with SQL 2000 boxes
$FileName = '' # Set a filename for the output
# Get List of sql servers to check
$sqlservers = Get-Content '' # serverlist, database query whatever

# Set SQL Query
$query = "WITH agg AS
(
SELECT
max(last_user_seek) last_user_seek,
max(last_user_scan) last_user_scan,
max(last_user_lookup) last_user_lookup,
max(last_user_update) last_user_update,
sd.name dbname
FROM
sys.dm_db_index_usage_stats, master..sysdatabases sd
WHERE
sd.name not in('master','tempdb','model','msdb')
AND
database_id = sd.dbid group by sd.name
)
SELECT
dbname,
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT dbname, last_user_seek, NULL FROM agg
UNION ALL
SELECT dbname, last_user_scan, NULL FROM agg
UNION ALL
SELECT dbname, last_user_lookup, NULL FROM agg
UNION ALL
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)
GROUP BY
dbname
ORDER BY 1;
"
#Open Excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
    # Get the time SQL was restarted
    $svr = New-Object 'Microsoft.SQLServer.Management.Smo.Server' $SQLServer
    $db = $svr.Databases['TempDB']
    $CreateDate = $db.CreateDate

    #Run Query against SQL Server
    $Results = Invoke-Sqlcmd -ServerInstance $sqlServer -Query $query -Database master
    # Add a new sheet
    $ws = $wb.Worksheets.Add()
    $name = "$sqlserver"
    # Name the Sheet
    $ws.name = $Name
    $cells = $ws.Cells
    $xl.Visible = $true
    #define some variables to control navigation
    $row = 2
    $col = 2
    $cells.item($row, $col) = $SQLServer + ' Was Rebooted at ' + $CreateDate
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $row = 3
    $col = 2
    # Set some titles
    $cells.item($row, $col) = "Server"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $col++
    $cells.item($row, $col) = "Database"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 40
    $col++
    $cells.item($row, $col) = "Last Read"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 20
    $col++
    $cells.item($row, $col) = "Last Write"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 20
    $col++

    foreach ($result in $results) {
        # Check if value is NULL
        $DBNull = [System.DBNull]::Value
        $LastRead = $Result.last_read
        $LastWrite = $Result.last_write

        $row++
        $col = 2
        $cells.item($Row, $col) = $sqlserver
        $col++
        $cells.item($Row, $col) = $Result.dbname
        $col++
        if ($LastRead -eq $DBNull) {
            $LastRead = "Not Since Last Reboot"
            $colour = "46"
            $cells.item($Row, $col).Interior.ColorIndex = $colour
            $cells.item($Row, $col) = $LastRead
        }
        else {
            $cells.item($Row, $col) = $LastRead
        }
        $col++
        if ($LastWrite -eq $DBNull) {
            $LastWrite = "Not Since Last Reboot"
            $colour = "46"
            $cells.item($Row, $col).Interior.ColorIndex = $colour
            $cells.item($Row, $col) = $LastWrite
        }
        else {
            $cells.item($Row, $col) = $LastWrite
        }
    }
}

$xl.DisplayAlerts = $false
$wb.Saveas($FileName)
$xl.quit()
Stop-Process -Name *excel*

 

 

What it does is place the query in a variable. Get the contents of the SQL Server text file holding all my known SQL Servers and runs the query against each of them storing the results in a variable. It then creates an Excel Workbook and a new sheet for each server and populates the sheet including a bit of colour formatting before saving it. The results look like this

usage excel

The tricky bit was understanding how to match the NULL result from the query. This was done by assigning a variable to [System.DBNull]::Value and using that.

Of course these stats are reset when SQL Server restarts so I also included the SQL server restart time using the create date property  of the TempDB. I gathered these stats for a few months before starting any rationalisation.

My next post will be about the next step in the process. You can get the script here