Voice of the DBA

Tag: syndicated

For your CIO: Building Technical Leaders

I might send this to a VP or CIO: Global CIO: A Framework For Developing Technical Leaders. It’s an article that talks about building senior technical leaders in your company, which I think is important if you view your Information Technology department as strategic.

That also means thinking about retention, and building an atmosphere where people want to work. That doesn’t mean you can’t have layoffs or need to keep every person, but management does have to show competent and hard working technical people that they are valuable. It should also involve some way to balance the disparate needs of ambitious people that you want to retain and those average employees that want a secure job and are willing to do the average jobs.

It also involves keeping a career path for technical people that doesn’t lead to management. Plenty of smart technical people want to keep growing, so give them a way to do so.

August 1, 2011
When Did That Restore Finish?
I saw this question come across Twitter under the #sqlhelp tag one day and was wondering myself. Someone suggested the default trace, I was thinking msdb.dbo.restorehistory and decided to check.

First I hit Books Online. It notes that the restore_date column means: Date and time of the restore operation. Can be NULL.

Very helpful (hopefully you read the sarcasm). This looks like a CS 101 comment, not very helpful and not detailed. Here’s a Connect item for more detail, and here’s one I submitted for clarification.

OK, time for testing. I first grabbed a copy of AdventureWorks since I assumed it would take at least a minute to restore. I restored a new database as “ADW_3”. It was around a minute and so I checked restorehistory:
```
USE msdb
GO
SELECT TOP 10 * FROM restorehistory
```
And I got this:

This was not terribly helpful. At the time I ran this, it was 9:32, so this appears to be the start date/time of the restore.

Then I checked the default trace: ‘
```
SELECT * FROM ::fn_trace_getinfo(0)

SELECT *
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_54.trc',0)
     INNER JOIN sys.trace_events e
          ON eventclass = trace_event_id
     INNER JOIN sys.trace_categories AS cat
          ON e.category_id = cat.category_id
WHERE databasename = 'ADW_3'
```
The first query gets the name of the file, which is put into the second query as the file source and that returned a number of results, of which the interesting ones were:

That isn’t terribly helpful either. I was hoping there would be a second event in the default trace, but there isn’t, even 10 minutes later when I checked. (I’m ever optimistic)

Then I thought “wouldn’t recovery run in a restored database? Isn’t that in the error log?”, so I decided to check there and found this:

The “starting up datbaase ‘adw_3’ is likely just after the log file is built and corresponds to a tenth of a second after the restore starts. I suspect the files are created first as part of the restore, but not marked in the error log. Then the database is started, and is marked a “restoring” immediately after.

There is a checkdb informational message, which is the last time that the command was run not on this database, but on the source database that created the backup file (bad, Steve, bad, 8 months old!).

Last we see that just about 6sec later there is the completed restore message.

No duration, but you can calculate that based on the “starting up” message.
July 28, 2011
A Guest at the Ranch

From the other side of the world, I had a guest at the ranch today.

Pinal Dave, a friend from India, sent me an email that he’d unexpectedly come to Denver this week. He had some time, so I invited him out to the ranch today for a few hours to sit and chat, see the horses, and spend some time.

We had a good time, and snapped a quick photo before he headed back to his hotel, and likely work knowing him.

I really enjoy the conversations I get to have with other successful and passionate people. They are some of the most interesting, and helpful, times I have in my career. This was definitely one of them.

July 18, 2011
SQL Server Backup – Inadvertent Striping

When I started working with SQL Server I got bit in the rear one day while I was testing backups. I was in the process of making a quick backup before I deployed some changes. I think this was in the Enterprise Manager days of SQL 2000, but it could have been v6.5. In any case, I had a dialog similar to this one that I’ve shown in Management Studio. For simplicity I’ve recreated this with AdventureWorks:

No biggie, right? I click add, and enter my new file for the backup:

I accept this, and highlight me new file and click “OK” to do the backup. That will work, won’t it?

Actually it won’t. or rather, it will but not in the way I expect.

What this will do is create a striped backup (search in this article for “striped). I will have my data in a backup, but I will need both of these files in order to do a restore as each will only have half my data.

It’s a rookie mistake, one that’s not possible with a script, which is why you should backup with scripts, not the GUI.

However if you notice this, delete all the files, add yours back, and make a full backup right away. Chances are that you might have broken your recovery chain. And make sure you let everyone know this is not what you want to encounter.

July 18, 2011