Schema for a table that will log backup purging

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Schema for a table that will log backup purging

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2013-09-16 : 11:20:41

Hi,

I do have a recurring job that will purge backup files from all our SQL servers if a customer is no longer a client.

I would like to create a table that will hold historical data when the folder is actually deleted. I came with this schema:


CREATE TABLE PurgedBackups
(
ID BIGINT IDENTITY(1,1) NOT NULL,
DBname varchar(30) NOT NULL,
PurgedDate DATETIME NOT NULL
)

Now ... not sure if I should create a new attribute to log fails or create a separate table, or just saving nothing. Should I create a separate attribute or table for that?

Here's the schema, in case I decide to log errors:


CREATE TABLE PurgedBackups
(
ID BIGINT IDENTITY(1,1) NOT NULL,
DBname varchar(30) NOT NULL,
Activity DATETIME NOT NULL,
Purged BIT NOT NULL,
ErrorMessage varchar(30) NULL
)

Which one should I use? Also, I must say that we are currently keeping our backups on an external USB drive which is rotated. So if a client actually dropped service, the job may have another round and delete a second set of backup files, creating another record on the purge database.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 15:24:12

Your first table seems to be designed only for holding data related to purging, but the second table has an activity column which suggests that it might be used to log other types of activities as well. If that is the case, first table certainly would not be sufficient.

Without knowing anything more about your requirments, and assuming you wan tot log only purges, I would create a table with the following columns:
ID
DBName
PurgeDate
PurgeResult
PurgeMessage

PurgeResult would be an enum (held in a reference table) that indicates various types of statuses: successful, cancelled, failed, and others perhpas. PurgeMessage would be a varchar column of sufficient lenght that indicates "no errors", or some other long error message that you may want to store.

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2013-09-17 : 11:38:05

Thanks James

I went for this one:


CREATE TABLE [dbo].[PurgedBackupsLog](
	[DBname] [nvarchar](50) NOT NULL,
	[Activity] [datetime] NOT NULL,
	[PurgingStatus] [bit] NOT NULL,
	[ErrorMessage] [nvarchar](100) NULL,
 CONSTRAINT [PK_PurgedBackups] PRIMARY KEY CLUSTERED 
(
	[Activity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I eliminated the ID as PK and used Activity as PK instead. I also created a NCI on DBname.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources