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

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

About these ads

One Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s