Ok, aside from being kinda sick still, my morning has been filled with interesting issues. Ahhh– the life of a prod DBA.
It started today with space issues–again. We’re just running out of space everywhere and it’s hard to even keep some DBs running because of the problems. So I’m having to shrink some DBs just to keep them active.
Now this one just gets me a little. Had a vendor come up to me to ask for help. He was on one of the prod servers and detached 3 of the DBs and couldn’t get them back. Turns out he had moved the files and renamed them and expected SQL to know where they were. He said he had problems initially and that’s why he did it, but he got stuck when he couldn’t detach them again to point SQL to the new files. So I got that worked out for him with relatively little effort.
Now this next one is just interesting. I just switched our backup routine on our big system to backup to 20 files. So the dev downstairs had a routine to restore it to a different server (I didn’t know that) and his routine was bombing. He had re-written it to use the 20 files, but it was still failing. Now, I’ll say that doing it the way he did doesn’t make him dumb. In fact, I could very easily see anyone making a similar mistake because to someone who doesn’t know backup intimately, it seems like the kind of thing you should be able to do. What he did was he was building a dynamic string to hold the file name values. So in the string he was saying something like this:
SET @BackupDatabaseFileNamePath = ‘DISK = N’ + ”” + ‘\\’ + @ProdIP + ‘\’ + LEFT(@BackupDatabaseFileNamePath,1) + ‘$’ +
RIGHT(@BackupDatabaseFileNamePath,(LEN(@BackupDatabaseFileNamePath)-2)) + ””
And so to that end, he was ending up with a string that looked like this:
DISK = ‘\\127.0.0.1\K$\SQLBackups\ServerName\DBName\01of20FullPRD20101102010001.BAK’,
And he did that 20 times, once for each file. So now his actual restore command looked like this:
DECLARE @file VARCHAR(100)
SET @file = ‘c:\SSISPkgMgmt.bak’
RESTORE DATABASE SSISPkgMgmt
FROM @file
WITH replace
RESTORE DATABASE PRD
FROM @BackupDatabaseFileNamePathInc
And that looks like it should work because when you print it, you wind up with a perfect backup cmd. The problem is that the backup cmd doesn’t work like that. It takes params, or flags if you will, right? And one of those flags is ‘DISK =’. That means that the flag itself is ‘DISK =’, not a string that contains that same text. It’s a subtle difference to us, but not to the backup cmd. So if you want to build a string like that for either a backup or a restore, then you have to build a string that contains the entire backup cmd and not just a single part that includes the params.
Here’s an example of something you can do though:
DECLARE @file VARCHAR(100)
SET @file = ‘c:\SSISPkgMgmt.bak’
RESTORE DATABASE SSISPkgMgmt
FROM @file
WITH replace
And what he was trying to do was this:
DECLARE @file VARCHAR(100)
SET @file = ‘DISK = ”c:\SSISPkgMgmt.bak”’
RESTORE DATABASE SSISPkgMgmt
FROM @file
WITH replace
If you run something like this you’ll see that SQL views it as a backup device because that follows the restore syntax.
So anyway, big fun again today too.