Identify SQL Table Backups

Earlier we discussed methods for executing quick SQL table backups and performing quick SQL table restores as a way to mitigate risk to data during certain operations.  In this article we are going to discuss some low-effort housekeeping methods to keep us from forgetting about the table backups we created.

While it is not a huge deal that a few table backups are hanging out in your databases, having a large number of these backups can make things disorganized and large table backups can take up space unnecessarily.  In general it is a good habit to keep your environment clean, but how much time and effort are we willing to spend doing so?  At some point the benefit of numerous housekeeping chores is outweighed by the time and effort of not only performing the chores but keeping track of them.

Therein lies the beauty of the instructions in this article: once created, the process of identifying old table backups is entirely automated.  It is true that one still does have to manually remove the tables designated - we do not want the machines to have too much autonomy, but by using Transact-SQL functions, SQL Server Agent and SQL Database Mail we can have the database server send us a list of old table backups on a regular basis.


Configuration

The first step is to create the stored procedure that will identify the old table backups.  This user stored procedure will be referenced by code in a SQL Agent job and is the heart of the process.

--SQL Script begin
USE MASTER
GO
if exists (select * from INFORMATION_SCHEMA.ROUTINES where SPECIFIC_NAME =
N'usp_TableBackupsOlderThan2Weeks')
DROP PROC usp_TableBackupsOlderThan2Weeks
GO
CREATE PROC usp_TableBackupsOlderThan2Weeks
as

exec sp_MSforeachdb
@command1='if (select count (*)
from [?].sys.objects
where name like ''F%[_]20%''
and type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, modify_date, GETDATE()) > 14
or name like ''F%bak%''
and type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, modify_date, GETDATE()) > 14) > 0
BEGIN
print ''?''
select cast (db_name (DB_ID(''?'')) + ''.'' + [?].sys.schemas.name + ''.'' + [?].sys.objects.name as char(55)) as ''Table Name'',
cast ([?].sys.objects.create_date as char (25)) as ''Created''
from [?].sys.objects
JOIN [?].sys.schemas on [?].sys.objects.schema_id = [?].sys.schemas.schema_id
where [?].sys.objects.name like ''F%[_]20%''
and [?].sys.objects.type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, [?].sys.objects.modify_date, GETDATE()) > 14
or [?].sys.objects.name like ''F%bak%''
and [?].sys.objects.type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, [?].sys.objects.modify_date, GETDATE()) > 14
order by ''Table Name''
Print ''

''
END'
--SQL Script end

The two most important parts of the above code are:

where name like ''F%[_]20%''

and

or name like ''F%bak%''

These are the sections that modify the SELECT statement with a WHERE clause that uses pattern matching and SQL wildcard characters to choose records that match the names of table backups created by our earlier script, which produces tables named something like JDE_PRODUCTION.PRODDTA.F0101_200906081807.  Note that in the first example the underscore character is not being used as a SQL wildcard, I am actually looking for an underscore, which is why it is enclosed in brackets.

I have also included a pattern match for tables that begin with 'F' and contain the string 'bak', a typically used naming convention for EnterpriseOne table backups.  You can add or remove additional clauses to tune the query for your particular environment but if all you are using to produce quick table backups is my Quick SQL Table Backups script, then the above will be sufficient.

Another important part of the code is the '> 14' portion of each WHERE clause section.  This dictates that we want records returned only for tables that are older than two weeks.  Feel free to modify this value to suit your needs.


The second configuration step is to create the notification delivery mechanism - a combination of a SQL Agent job and SQL Database Mail.  If you have not already configured Database Mail, here is a very good article on how to do so.


Create a SQL Agent job named E1_Identify SQL Table Backups Older Than 2 Weeks (or whatever time period you specified in the WHERE clause section).

Schedule the job to run every month, preferably on the same day every month.  I choose the second Monday of every month for mine but again, change to suit your needs.

Create a job step called Send Mail or something suitably witty or descriptive, it really doesn't matter much.  Specify Transact-SQL as the type and use the following code as the command:

--SQL Script begin
EXEC databaservername.msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
@recipients = 'email.address@domain.com;email.address2@domain.com',
    @subject = 'Table Backups Older Than 2 Weeks',
    @query = 'exec master.dbo.usp_TableBackupsOlderThan2Weeks',
       @body = 'These table backups are older than 2 weeks and can be removed:

'
--SQL Script end

Be sure to change the red-shaded items above to values that make sense for your system.  The 'profile name' variable should match a Database Mail profile that exists and that you wish to use to send the email. 

Note that the 'query' variable references the user stored procedure we create in the first configuration step. Also note that there is a full blank line in the value for the 'body' variable.  That exists for email formatting and the email is quite ugly without it.

Speaking of formatting, we can now look at what we will receive once a month in our email.


Results

The values specified in the sp_send_mail variables mean that we will receive an email with the subject 'Table Backups Older Than 2 Weeks', an initial body 'These table backups are older than 2 weeks and can be removed:' and the results of the query 'exec master.dbo.usp_TableBackupsOlderThan2Weeks'.

The stored procedure specified in the query, master.dbo.usp_TableBackupsOlderThan2Weeks, makes use of the undocumented stored procedure sp_MSforeachdb which means that we will get the list of tables matching the patterns specified in the WHERE clauses grouped by database. Databases with no tables matching the specified patterns will not be included in the result set.

The emailed results will look something like this:


These table backups are older than 2 weeks and can be removed:


JDE812
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
dbo.F9006_bak                  Mar 25 2009  4:11PM       Mar 25 2009  4:11PM     
SVM812.F986101_bak2            Feb  6 2009  7:14PM       Feb  6 2009  7:14PM     
SY812.F986101_200907151136     Jul 15 2009 11:36AM       Jul 15 2009 11:36AM     


JDE_PRODUCTION
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
dbo.F98865_bak                 Dec 17 2008  7:15PM       Dec 17 2008  7:15PM     
PRODDTA.F0011_200910061837     Oct  6 2009  6:37PM       Oct  6 2009  6:37PM     


JDE_DV812
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
DV812.F983051_200912021511     Dec  2 2009  3:11PM       Dec  2 2009  3:11PM     


JDE_DEVELOPMENT
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
TESTDTA.F989998_200910230855   Oct 23 2009  8:55AM       Oct 23 2009  8:55AM     
TESTDTA.F989999_200910230855   Oct 23 2009  8:55AM       Oct 23 2009  8:55AM     





As you can see, the email contains tables that match the patterns specified in the WHERE clauses and are older than 2 weeks.  The tables are grouped by database and ordered by schema name.table name with the columns Created Date/Time and Modified Date/Time provided as additional information.


Summary

A certain amount of housekeeping is necessary to keep things organized in your SQL Server installations.  However, keeping track of such tasks can be burdensome and we'd like to do whatever we can to make use of the features of SQL Server to automate items, essentially putting as much of the housekeeping on autopilot.  Using Transact-SQL code, SQL Server Agent and Database Mail we are able to be notified on a periodic basis when there are table backups that can be removed.



Related postings:

Quick SQL Table Backup
http://jeffstevenson.karamazovgroup.com/2009/06/quick-sql-table-backup.html

Quick SQL Table Restore
http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html