This month is an interesting T-SQL Tuesday topic, and it’s brought to us by Andy Mallon, with the topic of the same old issues we’re still dealing with. I think that’s an interesting issue, since I do find myself answering the same old questions over and over.

If you’ve never participated, T-SQL Tuesday is a day when people should publish a post on the specified topic. This is a way to generate some posts and interest, and perhaps learning, about a topic. Share your thoughts, either on the second Tuesday of each month, or catch up later on your blog.

We Still Don’t Restore

I’d like to write something about T-SQL, and I could. Certainly SELECT * is an issue, or why we should not use old style joins (that’s almost gone), but there’s a SQL Server topic that I think bears repeating.

A backup isn’t enough. You must also test restores.

The reason isn’t complex, but plenty of people still don’t seem to understand why a backup isn’t enough. After all, if I copied a file, or I ran BACKUP DATABASE successfully, isn’t that enough?

Suppose you had an issue at 3am and needed to restore a database on a new instance. You run this T-SQL:

USE [master];
RESTORE DATABASE [Finances]
FROM DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Finances.bak'
WITH FILE = 1,
    NOUNLOAD,
    STATS = 5;
GO

And you get this error:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint '0xD9B9E685D465E29C11E15346D995DEF59E53B4A3'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

That means testing restores.

It’s 2016. I shouldn’t have to remind anyone of this.

Filed under: Blog Tagged: administation, sql server, syndicated, T-SQL Tuesday