Career Limiting Moves – Assigning Blame

I worked for a vendor implementation partner in the early part of my career, and oftentimes that meant having to set up a training lab, from unpacking rented PCs and plugging them in to getting a full training environment installed and configured.

This included the operating system (usually Windows NT 4 Workstation or Windows 98), installing all Windows Updates, and Microsoft Office, plus the vendor’s training software. It was easily a four- to six-hour long process per PC, though of course I could parallelise my tasks and set them up at roughly the same time.

One Friday, I was asked to go into the office over the weekend to make sure that around ten machines were ready for a Monday morning training session.

I asked that the PCs be installed with the operating system and Microsoft Office before I got there on Sunday because it would be quite time consuming to do everything from scratch. The person I asked said that they would be done on Saturday, so that I could go in on Sunday and at least enjoy half of my weekend.

Sunday rolled around and in I went, only to find that the PCs were still in their boxes. I was furious, so I phoned a colleague who worked for the vendor to find out why it hadn’t been done. The call went to voice mail, so she had an excellent quality recording, of me saying I was having to work the entire day on Sunday “to fix [redacted]’s f**ckup”.

Classy, right?

On Monday, the boss called me in. This was the managing director, not my immediate boss.

She proceeded to quote back to me my voice message verbatim. While surprised that my colleague had ratted me out, I of course was still upset and suggested that the person didn’t do their job and I was left to sort it out.

She explained to me that whether or not that was the case, the language was totally inappropriate and calling a vendor on the weekend for something that did not constitute an emergency was unprofessional. In any number of scenarios, I could have been fired for my behaviour.

Chastened, I took away several important lessons: it doesn’t matter whose fault something is. The job had to be done, and I was around to do it. Furthermore, it is important never to be caught bad-mouthing someone on the record, no matter how good a relationship you have with a vendor. It will always come back to bite you.

Have your own career limiting story to share? Find me on Twitter using @bornsql.

Career Limiting Moves – Saying No

IT departments get a lot of flak because members are accused of saying no to what staff think are perfectly reasonable requests.

In this second post of my series about career limiting moves, I’m going to tell you about the time I told someone no and immediately regretted it.

My second full-time job, after working in a call centre, was in a technical support role. I didn’t have my driver’s licence yet, so I was based out of the head office. My job was to make sure the network was running, software was kept up to date, consultants’ laptops ran properly, and so on.

The same large customer from last week’s post had a Microsoft Access database system that was written when 16-bit Windows was the big kid on the block. I’d offered to convert the VBA code in the database to 32-bit, so that it could run on Windows NT 3.51 and Windows NT 4 on servers, as well as Windows 98 on the desktop.

My immediate boss was a director in the company, and I had been told on several occasions that it was a flat structure. We all socialised as equals, so I never paid much attention to reporting lines.

My boss told me to work on this Access database upgrade, and say no to anyone I considered a distraction. I took that to heart.

Another director of the company had some issue or other with her laptop that was affecting her ability to work and generate income for the business. You know: her job. She came to me and said, “I need your help with this problem”, and of course I said, “I’ve been told that I cannot have any distractions while working on this Access project”.

My job was to provide technical support to a senior staff member, and I said no because I was busy on something that was, for all intents and purposes, not as important.

This was of course escalated very quickly to the managing director, who in turn shouted at my boss, who in turn shouted at me. If I recall correctly, my boss eventually helped his colleague with her important problem and only reamed me out after the fact.

The moral of the story is that my priority list is meaningless to other people. It’s all very well saying no to a customer, and in my trade everyone I offer a service to is a customer. I have to be very sure that if I do say no, I’ve made that decision based on a clear understanding of the customer’s needs, not just my impressions.

Career Limiting Moves – Dropping a Table

In this new series, I will share some things that took place early in my career that could have resulted in my looking for new work—but didn’t!

This post is also evidence that I haven’t always worked with SQL Server.

I used to work with a PeopleSoft implementation partner, sometime last century. One morning I was on site, at a large technology company in South Africa. My senior consultant was walking me through running a script using a tool called Data Mover, against the production HRMS database.

The script created a new table in a database, copied the data from the old table, fixed up all the referential integrity, and then dropped the old table.

For whatever reason, we ran the script in the Oracle SQL*Plus client, which Wikipedia generously describes as “the most basic” database client.

Cut to the part where we run the script. The DROP TABLE command was run successfully and committed, but the previous step where the data was moved had failed.

The entire table was gone.

The senior consultant who had walked me through that process realised what had happened, made a call to the database administrator, and the database was restoring in short order.

All work that had been done that morning was lost, and it took about 2.5 hours to restore the previous night’s backup.

Moral of the story: make sure you run the right script in the right tool, and make sure your backups are being tested regularly.

Saving Time By Splitting Files for a Backup

During a recent engagement with a client transferring large files from on premises to Azure Storage, we discovered that files over a certain size are automatically throttled, causing file uploads to take much longer than expected.

Through unscientific testing, we figured out that the throttling seems to kick in at around 30 GB.

I rustled up a T-SQL script, based on some online research, to split SQL Server backups, so no single file would be larger than 30 GB. As this is a one-off migration, it’s a bit of a hack and does not take database compression into account.

This script makes use of Ola Hallengren’s Maintenance Solution, because his backup process takes the parameter @NumberOfFiles.

-- Dabatabase name
DECLARE @dbName NVARCHAR(255) = N'Temp';
-- Backup path
DECLARE @backupPath NVARCHAR(255) = N'C:\Temp';
-- Max backup file size (in GB)
DECLARE @maxFileSize NUMERIC(10, 5) = 30;
-- Number of files needed
DECLARE @filesNeeded INT;

-- Calculate number of files needed at @maxFileSize
ROUND(FILEPROPERTY([a].[name], 'SpaceUsed') / 128.0, 2))) /
(@maxFileSize * 1024.0))
FROM [sys].[database_files] [a];

-- Run full copy-only backup using number of files specified
-- (Requires Ola Hallengren's Maintenance Solution)
EXEC [master].[dbo].[DatabaseBackup] @Databases = @dbName,
@Directory = @backupPath,
@BackupType = 'FULL',
@Compress = 'Y',
@CopyOnly = 'Y',
@CheckSum = 'Y',
@NumberOfFiles = @filesNeeded,
@Verify = 'Y';

You don’t need to worry about data compression with this script because two 15 GB files will transfer just as quickly as a single 30 GB file. Provided you generate your restore script correctly, the number of files does not matter.

Think you have a way to improve this script? Let me know on Twitter at @bornsql .

Expiration Date on Database Backups

What does the expiration date mean on SQL Server database backups?

In SQL Server Management Studio, there’s an option in the Back Up Database wizard which explicitly provides an option for “Backup set will expire”. But until this week, I had never given it much thought.

This feature has been there for as long as I can remember. Does it mean that we can’t restore databases if the expiry date has passed? Maybe it has something to do with backing up to tape.

Let’s find out.

Test 1: Restore a database with an old expiry date

Using the EXPIREDATE option as specified in SQL Server Books Online, we will perform a simple backup using last week Saturday as our parameter.

USE [master];
CREATE DATABASE [BackupExpiryTest];
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'
WITH EXPIREDATE = N'2016-04-29 00:00:00';

Now let’s see if restoring the database today, 3 May 2016, still works. To be extra sure, we will drop the database we’ve just backed up.

USE [master];
DROP DATABASE [BackupExpiryTest];
FROM DISK = N'D:\Temp\BackupExpiryTest.bak';

Processed 344 pages for database 'BackupExpiryTest', file 'BackupExpiryTest' on file 1.
 Processed 2 pages for database 'BackupExpiryTest', file 'BackupExpiryTest_log' on file 1.
 RESTORE DATABASE successfully processed 346 pages in 0.068 seconds (39.723 MB/sec).

The EXPIREDATE option has no effect on whether we can restore the database.

What is it for, then? Let’s go and read Books Online, specifically the BACKUP command.

In the section labelled Media Set Options, we see the following explanation:

Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Even further down, under INIT, we see the following:

By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

  • Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.

Now we’re getting somewhere. If you back up your database to an existing file, the default setting (NOINIT) is to append that backup to the file. If you explicitly use INIT, however, it will overwrite the backup file and delete any existing backups, unless the EXPIREDATE value is a date sometime in the future.

Test 2: Append to existing backup file

Let’s append to our existing backup and set this backup to expire on Friday this week:

USE [master];
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'
WITH EXPIREDATE = N'2016-05-07 00:00:00';

Processed 344 pages for database 'BackupExpiryTest', file 'BackupExpiryTest' on file 2.
 Processed 2 pages for database 'BackupExpiryTest', file 'BackupExpiryTest_log' on file 2.
 BACKUP DATABASE successfully processed 346 pages in 0.055 seconds (48.161 MB/sec).

Notice that it says “file 2” in the output. This indicates that the backup we just performed is the second one in the backup file (or media set). If we wanted to restore this particular backup, we would have to specify WITH FILE = 2 in the restore script.

Now let’s try overwriting everything in the existing backup file using the INIT option:

USE [master];
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'

Msg 4030, Level 16, State 1, Line 1
The medium on device 'D:\Temp\BackupExpiryTest.bak' expires on May  7 2016 12:00:00:000AM and cannot be overwritten.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


The big hammer

If we look further down the Books Online entry about BACKUP, we will see the following:

To override these checks, use the SKIP option.

Sure, let’s try overriding it:

USE [master];
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'

Processed 344 pages for database 'BackupExpiryTest', file 'BackupExpiryTest' on file 1.
 Processed 2 pages for database 'BackupExpiryTest', file 'BackupExpiryTest_log' on file 1.
 BACKUP DATABASE successfully processed 346 pages in 0.056 seconds (48.139 MB/sec).


If you want to store multiple database backups in a single file, and want to control when these backups can be overwritten, you can set an expiration date with EXPIREDATE. Then the INIT option overwrites any backups that have not yet expired.

However, this can easily be overridden by the SKIP command. So this raises the question: if using the SKIP option simply overrides any expiration date in the backup file, why use EXPIREDATE in the first place?

I have never used expiration dates with my backups, and I don’t think I’m going to start now. I see no benefit in reusing existing files to append database backups. I will stick to using just one database backup per file, with a strong naming convention (including the date and time of the backup).

This way, dates in the file name make it easy to see at a glance when backups were taken, and then I have a higher confidence rating that files can be managed without having to scan their contents. Additionally, files older than a certain date can be cleaned out by a maintenance plan.