Trigger Happy Rotating Header Image

You learn something new…

Test your backups now

Hello – just a quick post today to remind you to test your backups.  Don’t just look to see if the file gets created and copied offsite, actually restore it somewhere and run CheckDB on it.

I have an agent job that does this for me every day for some of my clients.  It might seem anal, but you’d want to automate it anyway, and once it is set up, it just runs and you can forget about it (more or less) – at least until you get an alert that it failed, which happened to me this morning.  When I looked into it, I found that RESTORE simply errors out when trying to read the file that BACKUP wrote only hours before.  BACKUP reported no errors when writing the file, and as far as it was concerned, the backup was done, but RESTORE just barfed – the file is no good.  If I hadn’t been testing my backups, I would never have known my backup was no good unless I needed it which, I think we can agree, is a *really* bad time to find something like that out.

So what happened to the backup?  What was wrong with it?  I could probably look into it and figure it out, but much like Neo’s ability to dodge bullets in the Matrix, it’s much better if I don’t have to…  I caught it right away and took another backup (which, of course, I immediately tested).  If it happens more than once in a blue moon, I’ll look into it, but this one I’ll just delete and chalk up to an anomaly.  I’m a happy DBA and my client’s data is safe for another day.

I am not aware of any tools that automate backup testing for you, although I admit that I haven’t really looked into it.  The script I wrote is pretty straightforward and I can share it with you – just contact me

Do your T-SQL variables have the right values?

For some of us who are bewildered by even the code we write ourselves, this might be a question we ask ourselves in more circumstances than we’d like to admit, but what I’m talking about here is assigning values to a variable with a SELECT statement, and expecting the value to be null if the query didn’t find anything.  This can be particularly nefarious in cursor loops as I will show.  First, to set up the scenario, run the following script.  It creates two temp tables – one with 10 rows and one with 5:

IF OBJECT_ID('tempdb..#one') IS NOT NULL DROP TABLE #one
IF OBJECT_ID('tempdb..#two') IS NOT NULL DROP TABLE #two
CREATE TABLE #one (number INT IDENTITY (1,1))
CREATE TABLE #two (number INT IDENTITY (1,1))
GO
INSERT #one DEFAULT VALUES
GO 10
INSERT #two DEFAULT VALUES
GO 5
SELECT * FROM #one
SELECT * FROM #two

number
-----------
1
2
3
4
5
6
7
8
9
10
(10 row(s) affected) number
-----------
1
2
3
4
5
(5 row(s) affected)

Now we will use a cursor loop to go through the first (10-record) table and look for matching values in the second table – we should obviously only find 5 matching values – and display our findings.  This is what it *should* look like:

DECLARE @CR CURSOR, @Number INT, @Found INT
SET
@CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT
number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR
('Looking for number %i...',0,0,@Number)
  
SET @Found =(SELECT number FROM #two WHERE number = @Number)
  
IF @Found IS NOT NULL
      
RAISERROR('Found: %i',0,0,@Found)
  
FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking
for number 2...
Found: 2
Looking
for number 3...
Found: 3
Looking
for number 4...
Found: 4
Looking
for number 5...
Found: 5
Looking
for number 6...
Looking
for number 7...
Looking
for number 8...
Looking
for number 9...
Looking
for number 10...

Of course, it only found the five (by the way, please don’t take this as a recommendation to use cursors for this type of thing – I’m illustrating an unrelated point here…) How about the following, though – there is a subtle difference – do you see it right away?

DECLARE @CR CURSOR, @Number int, @Found int
SET
@CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT
number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR
('Looking for number %i...',0,0,@Number)
  
SELECT @Found = number FROM #two WHERE number = @Number
  
IF @Found IS NOT NULL
      
RAISERROR('Found: %i',0,0,@Found)
  
FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking
for number 2...
Found: 2
Looking
for number 3...
Found: 3
Looking
for number 4...
Found: 4
Looking
for number 5...
Found: 5
Looking
for number 6...
Found: 5
Looking
for number 7...
Found: 5
Looking
for number 8...
Found: 5
Looking
for number 9...
Found: 5
Looking
for number 10...
Found: 5

So “SELECT @Var =” and “SET @Var =” are the same thing, right? Well, it appears not.  Using the SELECT method, the variable is not resetting to NULL.  There is a simple explanation for this.  The SET method is setting the variable to the output of a query – that query did not return anything which is a missing value – the very definition of NULL – but the SELECT method is changing the variable as part of the query.  Since the query returns no rows, the variable assignment never takes place so the value of the variable does not change.

You may want the variable to change to NULL and you may not – so choose your method accordingly, but I have seen code that uses the SELECT method followed by a test for NULL clearly expecting the variable to be nulled if no record is found.

Exercise:  What happens if you use an aggregate like MAX() with the SELECT method – do you know what it will do before you try it?  Try it and see if you’re right (hint:  aggregate queries always return at least one row).

Be careful out there – the devil is in the details.


Transfer files with RDP

In the spirit of “you learn something new every day” I’ve been using Remote Desktop for years.  I use it almost every day.  Sometimes I’m remoted into machines that are remoted into other machines and so on (it can be very confusing).  But I did not know that you can map a drive on the machine you’re remoting into back to the machine you are remoting from.

In a lot of scenarios you are remoting into machines on the same network so transferring files is not an issue.  Sometimes, though, you’re remoting into a machine on the other end of the internet – it may not be running an FTP service or it may just be a pain to upload files to somewhere that this machine can download them from securely or conveniently. 

If, before you establish the RDP connection, you go into options and look at the local resources tab (and depending on your version of RDP, click the “more” button) you’ll see you can check off “drives”.  Once in the session, you’ll see the local drives under “My Computer”.  It’s not the fastest interface, but it is pretty slick.