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

Quick SQL Table Restore

A while back I discussed a method to do quick SQL table backups.  I usually create backups of tables prior to taking an action that has the potential to create the need to restore that table's data.  It's just a good idea, is easier than taking a full backup and gives you a readily available source of the original data should something go wrong with the changes you make.

Not that it has ever happened to me...but occasionally the need may arise to restore this data to the table you just butchered.

If you used the script in quick SQL table backups you ended up with a table backup named something like PRODDTA.F0101_200912071424 with PRODDTA being the schema, F0101 the table name and 200912071424 representing the date and time as YYYYMMDDHHMM.

We can use INSERT INTO to restore the data from this backup table to the original table but it requires us to truncate the original table, deleting all existing records.  The INSERT command appends records and any unique constraints in place on the original table will be observed, resulting in a "Violation of PRIMARY KEY constraint" error if you attempt to restore the data without clearing the original table.

Truncating any table is not a task lightly undertaken and the pucker factor can be pretty high.  Relax though, we do have a copy of the data in a table backup right?


To clear the original table we use the TRUNCATE command in the form:

TRUNCATE TABLE databasename.schemaname.originaltablename

--SQL Script begin
TRUNCATE TABLE JDE_PRODUCTION.PRODDTA.F0101
--SQL Script end


With the original table suitably cleared we can move forward with putting the backup table's data back into the original using this form:

INSERT INTO databasename.schemaname.originaltablename SELECT * from databasename.schemaname.backuptablename

--SQL Script begin
INSERT INTO JDE_PRODUCTION.PRODDTA.F0101 SELECT * from JDE_PRODUCTION.PRODDTA.F0101_200912071424
--SQL Script end


That takes care of getting the data back into the table but we have one last step to complete the recovery - rebuilding indexes and updating statistics.  While the data being restored to the original table is the exact same as what existed before, the storage engine needs to be re-taught what data is where by rebuilding the B-tree for the original table's indexes.

Since rebuilding indexes also accomplishes the goal of updating statistics we are going to execute the index rebuild only in this form:

ALTER INDEX ALL ON databasename.schemaname.originaltablename
            REBUILD

--SQL Script begin
ALTER INDEX ALL ON JDE_PRODUCTION.PRODDTA.F0101
REBUILD
--SQL Script end


That completes our quick SQL table restore.   You're back up and running with minimal interruption.


Related postings:

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

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html

Quick SQL Table Backup

Occasionally one needs to perform an action in either EnterpriseOne or SQL Server that places the data in one or more tables at risk. Examples include an index or (obviously) a table generation in OMW, a direct update to the data using a query, the first run of a custom UBE that updates data, etc. Best practices dictate that you should get a backup of the data.

Rather than using the time consuming process of backing up the entire database, and in the absence of table-specific backup/restore tools, one can quickly and easily make a copy of the table using standard SQL T-SQL code.

The command is SELECT INTO, or actually a combination of the SELECT command and the INTO clause. The basic statement looks something like


SELECT *
INTO new_table_name
FROM old_tablename

or

SELECT *
INTO JDE_PRODUCTION.PRODDTA.F0101_BAK
FROM JDE_PRODUCTION.PRODDTA.F0101


While this simple piece of code is sufficient to perform the copy, several problems arise. First, the code above requires you to correctly specify both the source and target table. Also, the SELECT INTO method will create a new table but it will not overwrite a table with the existing name. So, unless you feel like checking for the existence of a table named "JDE_PRODUCTION.PRODDTA.F0101_BAK" every time you perform this action I would suggest using the code below.

In this version we get a uniquely named table as long as you don't run it twice within a minute. The name will consist of the original table name plus characters representing the year, month, day, hour and minute. To do this, we grab the current date and time using GETDATE, convert it to text, replace characters like spaces, commas, etc. that we do not want in the table name, build this into a string, then use EXEC (@SQL) to run the query.

The code looks like this:

--SQL Script begin
DECLARE @Tablename NVARCHAR(500)
DECLARE @BuildStr NVARCHAR(500)
DECLARE @SQL NVARCHAR(500)
SET @Tablename = 'JDE_PRODUCTION.PRODDTA.F0101'
SET @BuildStr = CONVERT(NVARCHAR(16),GETDATE(),120)
SET @BuildStr = REPLACE(REPLACE(REPLACE(REPLACE(@BuildStr,'
',''),':',''),'-',''),' ','')
SET @SQL = 'select * into '+@Tablename+'_'+@BuildStr+' from '+@Tablename
SELECT @SQL
--Remove dashes on the line below to execute
--EXEC (@SQL)
--SQL Script end

and generates a query that looks like this:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F01012


In the example above we have specified the three part name "JDE_PRODUCTION.PRODDTA.F0101" in the @Tablename variable. You would change this variable to specify your table. Figured I'd mention that 'cause you just never know.

As constructed above, the query runs SELECT @SQL and will simply return the query string we built. It will not execute the copy. Once you are satisfied that the query string built is correct, remove the dashes from in front of EXEC (@SQL) to run the table copy.

You will see something like this when properly executed:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F0101

(1 row(s) affected)

(65436 row(s) affected)


Once you have completed the quick table copy you can now continue with the action that placed the data in danger knowing that you have a copy of the data.

Some caveats apply: the copy only brought over the data. No indexes exist on the backup copy of the table. Also, you now have an easily forgotten extra copy of what might be a very large table. Clean it up as a part of your scheduled maintenance, set a reminder to delete it later or use this method to automatically identify SQL Table Backups for removal.


Related postings:

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

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html