Refreshing A SQL Mirrored Database Using Powershell


SQL mirroring is a means of providing high availability for your SQL database. It is available in Standard Edition and although the feature is deprecated it is still widely utilised. You can read more about it on MSDN here and Jes Borland wrote a useful post answering many questions here

There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with mirroring. There are other scenarios but this post covers the automation of restoring a mirrored database from a backup.

I have mentioned before and no doubt I shall again, John Sansom wrote a great post about automation and I am a strong follower of that principle.

To refresh a SQL mirror the following steps are required, there are some gotchas that you need to be aware of which I will discuss later

remove mirroring
restore principle database from backup
perform a full backup of the principle database
perform a transaction log backup of the principle database
restore both backups on the mirror server with no recovery
recreate mirroring
resolve orphaned users
check mirroring status

Regular blog followers will know that I prefer to use Powershell when I can (and where it is relevant to do so) and so I have used Powershell to automate all of the steps above

The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.

Set variables as follows, the last three variables set the types for the backup action type and device type and do not need to be altered.

# Set up some variables

"$PrincipalServer = '' # Enter Principal Server Name
$MirrorServer = '' # Enter Mirror Server Name
$DBName = '' # Enter Database Name
$FileShare = '' # Enter FileShare with trailing slash
$LocationReplace = $FileShare + $DBName + 'Refresh.bak'
$LocationFUll = $FileShare + $DBName + 'formirroring.bak'
$LocationTran = $FileShare + $DBName + 'formirroring.trn'

$PrincipalEndPoint = 'TCP://SERVERNAME:5022' # Change as required
$MirrorEndpoint = 'TCP://SERVERNAME:5022' # Change as required
$WitnessEndpoint = 'TCP://SERVERNAME:5022' # Change as required

$Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
$File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File</PRE></PRE>


After some error checking the first thing is to create server and database SMO objects

"# Create Server objects
$Principal = New-Object Microsoft.SQLServer.Management.SMO.Server $PrincipalServer
$Mirror = New-Object Microsoft.SQLServer.Management.Smo.server $MirrorServer

#Create Database Objects
$DatabaseMirror = $Mirror.Databases[$DBName]
$DatabasePrincipal = $Principal.Databases[$DBName]</PRE></PRE>

(Added Extra – Use New-ISESnippet to create a SMO Server Snippet and use CTRL + J to find it

"New-IseSnippet -Title SMO-Server -Description "Create A SQL Server SMO Object"
-Text "`$srv = New-Object Microsoft.SqlServer.Management.Smo.Server `$server"


Remove Mirroring

Before we can restore the database we need to remove mirroring


restore principle database from backup

Once mirroring has been removed we can restore the database. Stuart Moore’s Great Series provides all the code you need to backup and restore databases with Powershell. There is however a bug which can catch you out. Here’s the code

"$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
#Perform Restore

The bug is as follows, if your restore is going to take longer than 10 minutes and you are using an earlier version of SQL than SQL 2012 SP1 CU8 then you will find that the restore fails after 10 minutes. This is the default timeout. You may try to set the


Value to a larger value or 0 and this will work after SQL 2012 SP1 CU8 but prior to that you will still face the same error. The simple workaround is to use Invoke-SQLCmd2 and to script the restore as follows

"#Set up Restore using refresh backup

$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
#Perform Restore
$restore.sqlrestore($PrincipalServer) # if query time < 600 seconds
# $query = $restore.Script($PrincipalServer) # if using Invoke-SQLCMD2

perform a full and transaction backup of the principle database

We need to perform a full and transaction log backup to set up mirroring. Again you may need to use the script method if your backup will take longer than 600 seconds. You have to take another full backup or you will receive errors about gaps in the log chain

"# Set up Full Backup
$Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
$Backup.Action = $Full
$Backup.BackupSetDescription = “Full Backup of “ + $DBName
$Backup.Database = $DatabasePrincipal.Name
$BackupDevice = New-Object –TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationFull,$File)
# Perform Backup
# $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds

# Invoke-Sqlcmd2 –ServerInstance $PrincipalServer –Database master –Query $query –ConnectionTimeout 0 # comment out if not used

#Setup Trans Backup
$Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup|Out-Null
$Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
$File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
$Backup.Action = $Tran
$Backup.BackupSetDescription = “Log Backup of “ + $DBName
$Backup.Database = $DBName
$BackupDevice = New-Object –TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null
# Perform Backup
# $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds

# Invoke-Sqlcmd2 –ServerInstance $PrincipalServer –Database master –Query $query –ConnectionTimeout 0 # comment out if not used

Restore both backups on the mirror server with no recovery

To complete the mirroring set up we need to restore the backups onto the mirror server with no recovery as follows

"#Set up Restore of Full Backup on Mirror Server
$restore = New-Object -TypeName Microsoft.SqlServe r.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationFUll,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.NoRecovery = $true
$restore.sqlrestore($MirrorServer) # if query time < 600 seconds
# $query = $restore.Script($MirrorServer) # if using Invoke-SQLCMD2

# Invoke-Sqlcmd2 -ServerInstance $MirrorServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used

# Set up Restore of Log Backup on Mirror Server
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.NoRecovery = $true

Recreate mirroring

You recreate mirroring in the same way as you would if you were using T-SQL simply add the principal endpoint to the mirror, and the mirror and witness endpoints to the principal

"#Recreate Mirroring
$DatabaseMirror.MirroringPartner = $PrincipalEndPoint
$DatabasePrincipal.MirroringPartner = $MirrorEndpoint
$DatabasePrincipal.MirroringWitness = $WitnessEndpoint

Resolve orphaned users

You will need to resolve any users and permissions on your destination servers. I do not know a way to do this with PowerShell and would be interested if anyone has found a way to replace the password or the SID on a user object, please contact me if you know.

Many people do this with the sp_rev_logins stored procedure which will create the T-SQL for recreating the logins. However, Powershell cannot read the outputs of the message window where the script prints the script. If you know that your logins are staying static then run sp_rev_logins and store the output in a sql file and call it with Invoke-SQLCmd2

"$SQL = ‘’ #Path to File
Invoke-Sqlcmd2 –ServerInstance $Server –Database master –InputFile $SQL

The other option is to set up a SSIS package following this blog post and call it from Powershell as follows

"Invoke-Command –ComputerName $Server –scriptblock {DTExec.exe /File “PATHTOPackage.dtsx”} 

This requires Powershell Remoting to have been set up on the server which may or may not be available to you in your environment.

IMPORTANT NOTE – The script does not include any methods for resolving orphaned users so you will need to test and then add your own solution to the script.

check mirroring status

Lastly you want to check that the script has run successfully and that mirroring is synchronised (I am from the UK!!) To do this I check that time and file used for the last database backup using this script

"#Check that correct file and backup date used

$query = "SELECT TOP 1 [rs].[destination_database_name] as 'database',
[rs].[restore_date] as 'restoredate',
[bs].[backup_finish_date] as 'backuptime',
[bmf].[physical_device_name] as 'Filename'
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC"

Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database msdb -Query $query |Format-Table -AutoSize –Wrap

and that mirroring has synchronised using the following Powershell command

"$DatabasePrincipal | select Name, MirroringStatus, IsAccessible |Format-Table -AutoSize

Depending on your needs you may add some error checking using the results of the above scripts. As I said at the top of the post, you can turn this script into a function and call it at will or add it to an Agent Job for regular scheduling or just kept in a folder ready to be run when required. The choice is yours but all usual rules apply. Don’t believe anything you read on this blog post, don’t run any scripts on production, test before running any scripts, understand what the code is doing before you run it or I am not responsible if you break anything

You can find the template script here

Enable CLR with Powershell

I had an email last night from someone who attended my PowerShell Box of Tricks session at SQL Saturday Exeter

He was getting an error whilst trying to set CLR Enabled during an automatic install and asked if I had any ideas. The error he had was related to Invoke-SQLcmd and the method he was calling the PowerShell script

I was unable to replicate his problem on my servers so I looked at other methods that may assist as well as following up with him to try and understand what was causing his issue. In doing so I worked out the following method to change the CLR Enabled setting by SMO and thought it worth a blog post to share

One way around his issue is to define and then call Invoke-SQLCmd2 by Chad Miller within his script. So his script would look in part as follows

However, I prefer to use SMO so I examined the Server SMO as follows notice the “.” for local server

$srv New-Object Microsoft.SQLServer.Management.SMO.Server .
$srv |gm

And noticed the Configuration property

$Config $srv.Configuration
$Config |gm

Enabled me to see the IsCLREnabled Property and using Get-Member I could see that the config value was settable

With this information I could write a simple script to alter the settings.

Prior to running the script

We then run the following script

Line 1 creates a Server SMO object there is a “.” to denote local server at the end of the line although you can use the server name as well

Line 4 sets the configvalue for the IsCLREnabled property

And Line 5 Alters the Config object, essentially running the reconfigure

After running the script

Hopefully this short post shows how easy it is to set SQL Server configuration values with Powershell using SMO

Any questions or comments please feel free to ask

Listing the SQL Server SysAdmins With PowerShell

A very short blog today just to pass on this little script.

I was required to list all of the SysAdmins across a large estate. Obviously I turned to PowerShell :-)

I iterated through my server list collection and then created a server SMO object and used the EnumServerRoleMembers method to display all of the sysadmin members


This will work on SQL2000 – SQL2012. You can see how you can easily change the rolename in the script to enumerate other server roles.

Another way you could do it is to use the query

“SELECT AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = ‘R’ AND =’sysadmin’
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id”

and pass that with Invoke-SQLCMD through to every server (if you had to use Powershell :-) ). That query won’t work with SQL 2000 though

How I Check Hundreds of SQL Agent Jobs in 60 Seconds with Powershell

Checking that your Agent Jobs have completed successfully is a vital part of any DBA’s responsibility. It is essential to ensure that all of the hard work you have put into setting up the jobs can be quickly and easily checked. In a large estate this can be very time consuming and if done manually prone to human error. I have repeatedly mentioned John Sansoms Blog Post entitled “The Best DBAs Automate Everything” and I follow that advice. Today I will share with you one fo the first scripts that I wrote.

When I started as a DBA I was told that my first job every morning was to check the Agent Jobs and resolve any errors. This is still something I do first before anything else. (Except coffee, experience has taught me that you get your coffee before you log into your computer otherwise on the bad days you can miss out on coffee for many an hour) I have two scripts to do this. The first sends me an email if the number of failed jobs on a server is greater than zero. This helps me to quickly and simply identify where to start in the case of multiple failures and is also a backup to the second script.

The second script runs on a different server and creates an excel worksheet and colour codes it. This makes it very simple to quickly scroll through the sheet and spot any red cells which designate failed jobs and also provides a nice easy to understand method to show management that on that specific day everything went well (or badly)

As with any Powershell script which manipulates Office applications you first need to create an object and add the workbook and worksheet to it. I also set a filename date variable and a Date variable for the Sheet.


When you use Powershell to manipulate Excel you can access individual cells by identifying them by Row and Column. I use this to create a description for the work book as follows

There are lots of properties that you can play with within Excel. As with any Powershell the best way to find what you need is to use the Get-Member Cmdlet. If you run


You will see that there are 185 Methods and Properties available to you (in Office 2013 on Windows 8.1)

The snippet above creates the following


As you can see we are going to colour code the Job Status according to the three available results Successful, Failed and Unknown. We are also going to colour code the date column to see when the job was last run, this will enable you to easily identify if the last time the job ran it was successful but last night it didn’t kick off for some reason.

The next step is a fairly standard loop through available servers by picking them from a SQLServers text file, a list of the server names (ServerName\Instance if required) that you wish to check. You could also just create an array of server names or pick them from a table with Invoke-SQLCmd but which ever way you do it you need to be able to iterate through the array and then the .Jobs Collection in the JobServer Namespace as follows



What the script then does is to use the following properties of the $Job object and write the Excel File according to the logic in the description


To finish up save the workbook to a share available to all of the DBA Team and quit Excel. Notice that I use a double whammy to make sure Excel is really gone. First I quit the .com object and then I stop the process. I do this because I found that on my server quitting the .com object left the Excel process running and I ended up with dozens and dozens of them. If you have Excel open before you run this script either comment out the last line or save your work (You should save your work anyway regulary!)

As always I take no responsibility for your environment, that’s your Job! Don’t run this on Production unless you know what it is doing and are happy that you have first tested it somewhere safely away from any important systems. Make sure that you understand the correct time to run this job and have qualified the impact on the box it is running on.

Here is a screen shot of the finished Excel Sheet


As you can see the Data Transfer Job needs investigation! The reason I add to yellow rows above and below each servers list of jobs is to help me identify any server that is not responding as that will be easily recognised as two lots of yellow with nothing between them

I have considered improving this script by inputting the data into a database and running a report from that database but have not had the need to do so yet.

Enjoy this script, you can find it here


If you have any questions please use the comment box below



Rationalisation of Database with Powershell and T-SQL part two

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

-- 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
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
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

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

You can find the script here

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

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 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
# 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

# Set SQL Query
$query = “WITH agg AS
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, dbname
sys.dm_db_index_usage_stats, master..sysdatabases sd
WHERE not in(‘master’,’tempdb’,’model’,’msdb’)
database_id = sd.dbid group by
last_read = MAX(last_read),
last_write = MAX(last_write)
SELECT dbname, last_user_seek, NULL FROM agg
SELECT dbname, last_user_scan, NULL FROM agg
SELECT dbname, last_user_lookup, NULL FROM agg
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)

#Open Excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()

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

# Get List of sql servers to check
$sqlservers = Get-Content ‘D:\SkyDrive\Documents\Scripts\Powershell Scripts\sqlservers.txt’

# 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
$ = $Name
$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).Columnwidth = 10
# Set some titles
$Cells.item($row,$col).Columnwidth = 10
$Cells.item($row,$col).Columnwidth = 40
$cells.item($row,$col)=”Last Read”
$Cells.item($row,$col).Columnwidth = 20
$cells.item($row,$col)=”Last Write”
$Cells.item($row,$col).Columnwidth = 20

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

if($LastRead -eq $DBNull)
$LastRead = “Not Since Last Reboot”
$colour = “46”
$cells.item($Row,$col).Interior.ColorIndex = $colour
$cells.item($Row,$col)= $LastRead
$cells.item($Row,$col)= $LastRead
if($LastWrite -eq $DBNull)
$LastWrite = “Not Since Last Reboot”
$colour = “46”
$cells.item($Row,$col).Interior.ColorIndex = $colour
$cells.item($Row,$col)= $LastWrite
$cells.item($Row,$col)= $LastWrite

$xl.DisplayAlerts = $false

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

Installing Windows Server 2003 on Hyper-v

A quick post, as much for next time (if there is one) in case I forget.

I created a new Virtual Machine in Hyper-v and installed Windows Server 2003 Standard but after the reboot I had no network connection despite setting it up to connect to my internal network.

I tried re-adding the network adapter in Hyper-v and rebooting which  didnt work so I checked Device Manager which displayed No Device

To fix this, I downloaded and installed Windows Server 2003 SP2 and then installed Integration Services from the Action Menu of the Hyper-V Virtual Machine Connection.

Then after a reboot I had a network adapter